explaining union

From: dag(at)interfree(dot)it ()
To: pgsql-cygwin(at)postgresql(dot)org
Subject: explaining union
Date: 2002-11-08 12:37:54
Message-ID: 20021108123754.31407.qmail@community10.interfree.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-cygwin

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!

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

Browse pgsql-cygwin by date

  From Date Subject
Next Message beakerboy@www.com 2002-11-08 14:37:00 Re: Postmaster will not start
Previous Message Jason Tishler 2002-11-08 12:01:31 Re: pg7.3b5 on cygwin 1.3.15-1 - Same error