Обсуждение: explaining union

Поиск
Список
Период
Сортировка

explaining union

От
dag@interfree.it ()
Дата:


I've noted a strange behaviour on where conditions,
a simple example:

create table temp1 (col1 integer, primary key (col1));
create table temp2 (col1 integer, primary key (col1));


explain
select * from (
select * from temp1
union all
select * from temp2) x
where col1 = 1;

Subquery Scan x  (cost=0.00..40.00 rows=2000 width=4)
  ->  Append  (cost=0.00..40.00 rows=2000 width=4)
        ->  Subquery Scan *SELECT* 1  (cost=0.00..20.00 rows=1000 width=4)
              ->  Seq Scan on temp1  (cost=0.00..20.00 rows=1000 width=4)
        ->  Subquery Scan *SELECT* 2  (cost=0.00..20.00 rows=1000 width=4)
              ->  Seq Scan on temp2  (cost=0.00..20.00 rows=1000 width=4)


it seems the external condition doesn't propagate on the internal subselect..

but it's only on union (or except) statements, not a subselect issue:
infact,

explain
select * from
(select * from temp1
) x
where col1 = 1;

Index Scan using temp1_pkey on temp1  (cost=0.00..4.82 rows=1 width=4)

analyzing and/or querying much larger tables makes no difference;



is there any way to improve the first plan?

TIA,
--g.






-----------------------------------------------------

Salve, il messaggio che hai ricevuto
� stato inviato per mezzo del sistema
di web mail interfree. Se anche tu vuoi
una casella di posta free visita il
sito http://club.interfree.it
Ti aspettiamo!

-----------------------------------------------------

Re: explaining union

От
dag@interfree.it ()
Дата:

crossposting from pgsql-cygwin (if it's fair;)

TIA,
--g.


>
>
>I've noted a strange behaviour on where conditions,
>a simple example:
>
>create table temp1 (col1 integer, primary key (col1)); create table temp2 (col1
>integer, primary key (col1));
>
>explain
>select * from (
>select * from temp1
>union all
>select * from temp2) x
>where col1 = 1;
>
>Subquery Scan x  (cost=0.00..40.00 rows=2000 width=4)   ->  Append
>(cost=0.00..40.00 rows=2000 width=4)
>        ->  Subquery Scan *SELECT* 1  (cost=0.00..20.00 rows=1000 width=4)
>              ->  Seq Scan on temp1  (cost=0.00..20.00 rows=1000 width=4)
>        ->  Subquery Scan *SELECT* 2  (cost=0.00..20.00 rows=1000 width=4)
>              ->  Seq Scan on temp2  (cost=0.00..20.00 rows=1000 width=4)
>
>it seems the external condition doesn't propagate on the internal subselect..
>
>but it's only on union (or except) statements, not a subselect issue: infact,
>
>explain
>select * from
>(select * from temp1
>) x
>where col1 = 1;
>
>Index Scan using temp1_pkey on temp1  (cost=0.00..4.82 rows=1 width=4)
>
>analyzing and/or querying much larger tables makes no difference;
>
>is there any way to improve the first plan?
>
>TIA, --g.
>
>-----------------------------------------------------
>
>Salve, il messaggio che hai ricevuto � stato inviato per mezzo del sistema
>di web mail interfree. Se anche tu vuoi
>una casella di posta free visita il
>sito http://club.interfree.it
>Ti aspettiamo!
>
>-----------------------------------------------------
>
>---------------------------(end of broadcast)--------------------------- TIP 2:
>you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>


-----------------------------------------------------

Salve, il messaggio che hai ricevuto
� stato inviato per mezzo del sistema
di web mail interfree. Se anche tu vuoi
una casella di posta free visita il
sito http://club.interfree.it
Ti aspettiamo!

-----------------------------------------------------