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. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
