Factoring where clauses through unions

On Friday April 4 2003 1:42, Jonathan Bartlett wrote:

I have a view that is based on several UNION clauses. I'm wondering, if
I select data from there, will it UNION first, and then apply my where
clauses, or can it factor my where clauses through the UNION? If it
doesn't do it automatically, is there any way to automate it?

For cases like this, you can usually construct a very simple test to get the
answer. But let's see your view creation command so we know what you're
talking about.

Ed

"Ed L." <pgsql@bluepolka.net> writes:

On Friday April 4 2003 1:42, Jonathan Bartlett wrote:

I have a view that is based on several UNION clauses. I'm wondering, if
I select data from there, will it UNION first, and then apply my where
clauses, or can it factor my where clauses through the UNION? If it
doesn't do it automatically, is there any way to automate it?

For cases like this, you can usually construct a very simple test to get the
answer.

A test case would tell him what the version he tests does. It would not
likely inform him that 7.3 will push down such conditions but prior
versions don't ...

regards, tom lane

On Friday April 4 2003 4:41, Tom Lane wrote:

"Ed L." <pgsql@bluepolka.net> writes:

On Friday April 4 2003 1:42, Jonathan Bartlett wrote:

I have a view that is based on several UNION clauses. I'm wondering,
if I select data from there, will it UNION first, and then apply my
where clauses, or can it factor my where clauses through the UNION?
If it doesn't do it automatically, is there any way to automate it?

For cases like this, you can usually construct a very simple test to
get the answer.

A test case would tell him what the version he tests does. It would not
likely inform him that 7.3 will push down such conditions but prior
versions don't ...

Can you provide a simple example of this change between 7.2.x and 7.3?

Ed

"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

Thank you so much!

That's exactly what I needed to know.

Jon

On Fri, 4 Apr 2003, Tom Lane wrote: