Re: Factoring where clauses through unions

"Ed L." <pgsql@bluepolka.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