Thread: Union strange explain

Union strange explain

From
"Gaetano Mendola"
Date:
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");




Re: Union strange explain

From
Stephan Szabo
Date:
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.





Re: Union strange explain

From
Tom Lane
Date:
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



Re: Union strange explain

From
"Gaetano Mendola"
Date:
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




Re: Union strange explain

From
Stephan Szabo
Date:
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. :)