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