Re: explaining union

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!

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