Re: Factoring where clauses through unions

"Ed L." <pgsql(at)bluepolka(dot)net> writes:
> Can you provide a simple example of this change between 7.2.x and 7.3?

Sure. Using the regression-test database (or any large table with an
index), 7.3 can do this:

regression=# explain select * from (
regression(# select * from tenk1 union all select * from tenk1 ) AS vv
regression-# where unique1 = 42;
QUERY PLAN

-----------------------------------------------------------------------------------------------
Subquery Scan vv (cost=0.00..12.00 rows=2 width=244)
-> Append (cost=0.00..12.00 rows=2 width=244)
-> Subquery Scan "*SELECT* 1" (cost=0.00..6.00 rows=1 width=244)
-> Index Scan using tenk1_unique1 on tenk1 (cost=0.00..6.00 rows=1 width=244)
Index Cond: (unique1 = 42)
-> Subquery Scan "*SELECT* 2" (cost=0.00..6.00 rows=1 width=244)
-> Index Scan using tenk1_unique1 on tenk1 (cost=0.00..6.00 rows=1 width=244)
Index Cond: (unique1 = 42)
(8 rows)

whereas the same query in 7.2 can't produce index scans, because the
WHERE condition is applied at the top level, not at the table scans:

regression=# explain select * from (
regression(# select * from tenk1 union all select * from tenk1 ) AS vv
regression-# where unique1 = 42;
NOTICE: QUERY PLAN:

Subquery Scan vv (cost=0.00..666.00 rows=20000 width=148)
-> Append (cost=0.00..666.00 rows=20000 width=148)
-> Subquery Scan *SELECT* 1 (cost=0.00..333.00 rows=10000 width=148)
-> Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148)
-> Subquery Scan *SELECT* 2 (cost=0.00..333.00 rows=10000 width=148)
-> Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148)

EXPLAIN

regards, tom lane