Thread: Factoring where clauses through unions
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? Thanks, Jon
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: > "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 >