[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] optimizer, view, union
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2005-06-23 19:28:50
Message-ID: 7743.1119554930 () sss ! pgh ! pa ! us
[Download RAW message or body]

Markus Bertheau <twanger@bluetwanger.de> writes:
> Can pg transform

> SELECT * FROM (
> 	SELECT 'foo' AS class, id FROM foo
> 	UNION ALL
> 	SELECT 'bar' AS class, id FROM bar
> ) AS a WHERE class = 'foo'

[ experiments... ]  Yes, if you spell it like this:

regression=# explain SELECT * FROM (
regression(# SELECT 'foo'::text AS class, id FROM foo
regression(# UNION ALL
regression(# SELECT 'bar'::text AS class, id FROM bar
regression(# ) AS a WHERE class = 'foo';
                               QUERY PLAN
-------------------------------------------------------------------------
 Append  (cost=0.00..105.60 rows=4280 width=4)
   ->  Subquery Scan "*SELECT* 1"  (cost=0.00..52.80 rows=2140 width=4)
         ->  Seq Scan on foo  (cost=0.00..31.40 rows=2140 width=4)
   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..52.80 rows=2140 width=4)
         ->  Result  (cost=0.00..31.40 rows=2140 width=4)
               One-Time Filter: false
               ->  Seq Scan on bar  (cost=0.00..31.40 rows=2140 width=4)
(7 rows)

If unadorned, the literals get caught up in some type-conversion issues.
(You don't really want them in the output of a view anyway; "unknown"
type columns are bad news.)

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
[prev in list] [next in list] [prev in thread] [next in thread]