Re: Factoring where clauses through unions

Thank you so much!

That's exactly what I needed to know.

Jon

On Fri, 4 Apr 2003, Tom Lane wrote:

> "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
>