Re: Union strange explain

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.