Thread: optimizer, view, union

Hi,

Can pg transform

SELECT * FROM (SELECT 'foo' AS class, id FROM fooUNION ALLSELECT 'bar' AS class, id FROM bar
) AS a WHERE class = 'foo'

into

SELECT 'foo' AS class, * FROM foo?

The subselect is a view here, that's why I don't just use the second
query.

Markus

--
Markus Bertheau <twanger@bluetwanger.de>

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=2140width=4)  ->  Subquery Scan "*SELECT* 2"  (cost=0.00..52.80 rows=2140 width=4)        ->  Result
(cost=0.00..31.40rows=2140 width=4)              One-Time Filter: false              ->  Seq Scan on bar
(cost=0.00..31.40rows=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