Thread: Union strange explain
Hi all,
I have a view in this form:
CREATE VIEW my_view AS
< QUERY-A>
UNION ALL
<QUERY-B>
Now if I do:
# explain <QUERY-A> WHERE login = 'asdadad';
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..7.08 rows=1 width=88)
-> Nested Loop (cost=0.00..5.05 rows=1 width=52)
-> Index Scan using user_login_login_key on user_login
(cost=0.00..2.02 rows=1 width=16)
-> Index Scan using idx_user_user_traffic on user_traffic ut
(cost=0.00..3.02 rows=1 width=36)
-> Index Scan using contracts_pkey on contracts c (cost=0.00..2.01
rows=1 width=36)
# explain <QUERY-B> WHERE login = 'asdadad';
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..4.22 rows=1 width=68)
-> Nested Loop (cost=0.00..3.20 rows=1 width=40)
-> Index Scan using user_login_login_key on user_login
(cost=0.00..2.02 rows=1 width=16)
-> Seq Scan on cas_subscription csub (cost=0.00..1.08 rows=8
width=24)
-> Seq Scan on cas_service cser (cost=0.00..1.01 rows=1 width=28)
if instead I do:
# explain select * from my_view where login = 'asdadad';
NOTICE: QUERY PLAN:
Subquery Scan foo (cost=367.73..393.27 rows=93 width=88)
-> Unique (cost=367.73..393.27 rows=93 width=88)
-> Sort (cost=367.73..367.73 rows=929 width=88)
-> Append (cost=84.59..321.95 rows=929 width=88)
-> Subquery Scan *SELECT* 1 (cost=84.59..303.59
rows=926 width=88)
-> Hash Join (cost=84.59..303.59 rows=926
width=88)
-> Hash Join (cost=81.57..262.53 rows=926
width=52)
-> Seq Scan on user_traffic ut
(cost=0.00..52.78 rows=2778 width=36)
-> Hash (cost=78.16..78.16 rows=1364
width=16)
-> Seq Scan on user_login
(cost=0.00..78.16 rows=1364 width=16)
-> Hash (cost=2.81..2.81 rows=81 width=36)
-> Seq Scan on contracts c
(cost=0.00..2.81 rows=81 width=36)
-> Subquery Scan *SELECT* 2 (cost=0.00..18.36 rows=3
width=68)
-> Nested Loop (cost=0.00..18.36 rows=3
width=68)
-> Seq Scan on cas_service cser
(cost=0.00..1.01 rows=1 width=28)
-> Materialize (cost=17.31..17.31 rows=3
width=40)
-> Nested Loop (cost=0.00..17.31
rows=3 width=40)
-> Seq Scan on cas_subscription
csub (cost=0.00..1.08 rows=8 width=24)
-> Index Scan using
user_login_pkey on user_login (cost=0.00..2.02 rows=1 width=16)
How it is possible that the two Subquery Scan have two completely different
plan ?
How I can obtain for the two subselect the same plan like is in a single
query ?
Ciao
Gaetano
--
#exclude <windows>
#include <CSRSS>
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");
On Thu, 4 Jul 2002, Gaetano Mendola wrote: > Hi all, > > I have a view in this form: > > CREATE VIEW my_view AS > < QUERY-A> > UNION ALL > <QUERY-B> > > Now if I do: > > # explain <QUERY-A> WHERE login = 'asdadad'; > NOTICE: QUERY PLAN: > > Nested Loop (cost=0.00..7.08 rows=1 width=88) > -> Nested Loop (cost=0.00..5.05 rows=1 width=52) > -> Index Scan using user_login_login_key on user_login > (cost=0.00..2.02 rows=1 width=16) > -> Index Scan using idx_user_user_traffic on user_traffic ut > (cost=0.00..3.02 rows=1 width=36) > -> Index Scan using contracts_pkey on contracts c (cost=0.00..2.01 > rows=1 width=36) > > # explain <QUERY-B> WHERE login = 'asdadad'; > NOTICE: QUERY PLAN: > > Nested Loop (cost=0.00..4.22 rows=1 width=68) > -> Nested Loop (cost=0.00..3.20 rows=1 width=40) > -> Index Scan using user_login_login_key on user_login > (cost=0.00..2.02 rows=1 width=16) > -> Seq Scan on cas_subscription csub (cost=0.00..1.08 rows=8 > width=24) > -> Seq Scan on cas_service cser (cost=0.00..1.01 rows=1 width=28) > > > > if instead I do: > # explain select * from my_view where login = 'asdadad'; It's probably not pushing the login='asdadad' condition down into the queries in the view so it's possibly doing a full union all followed by the condition (given that it's estimating a larger number of rows returned). I think there was some question about whether it was safe to do that optimization (ie, is select * from (a union [all] b) where condition always the same as select * from a where condition union [all] select * from b where condition ) This was discussed recently, but I forget what the final determination was.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I think there was some question about
> whether it was safe to do that optimization (ie,
> is select * from (a union [all] b) where condition
> always the same as
> select * from a where condition union [all]
> select * from b where condition
> )
> This was discussed recently, but I forget what the final determination
> was.
There wasn't any final determination --- it's still an open issue
whether there are any limitations the planner would have to consider
when trying to push down conditions into UNIONs. Offhand it seems
to me that the change is always safe when dealing with UNION ALL,
but I'm not quite convinced about UNION. And what of INTERSECT
and EXCEPT?
Another interesting question is whether there are cases where the
planner could legally push down the condition, but should not because
it would end up with a slower plan. I can't think of any examples
offhand, but that doesn't mean there aren't any.
regards, tom lane
Stephan wrote: > number of rows returned). I think there was some question about > whether it was safe to do that optimization (ie, > is select * from (a union [all] b) where condition > always the same as > select * from a where condition union [all] > select * from b where condition > ) Yes but with a View the condition is always extern. I'm understanding that I don't have any possibility to optimize that view, I'm wrong ? Ciao Gaetano
On Fri, 5 Jul 2002, Gaetano Mendola wrote: > Stephan wrote: > > number of rows returned). I think there was some question about > > whether it was safe to do that optimization (ie, > > is select * from (a union [all] b) where condition > > always the same as > > select * from a where condition union [all] > > select * from b where condition > > ) > > Yes but with a View the condition is always extern. > I'm understanding that I don't have any possibility to > optimize that view, I'm wrong ? Right now, I don't think there's much you can do using a view. Until we at least think we know what the safe conditions are to make that optimization, we can't do so since we might return incorrect results if the optimization is performed in a place it doesn't apply. If you want to do the legwork to show what (if any conditions) need to be met to do the optimization, we'd certainly be happy. :)