From: Tom Lane <tgl@sss.pgh.pa.us>
To: oheinz@stud.fbi.fh-darmstadt.de
Cc: Dmitry Tkach <dmitry@openratings.com>
Cc: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
Date: Fri, 25 Jul 2003 09:45:15 -0400
Message-ID: <8076.1059140715@sss.pgh.pa.us> (raw)
In-Reply-To: <1059123702.3f20f1f689c61@stud.fbi.fh-darmstadt.de>
References: <3F1FCCE2.47ACF401@rodos.fzk.de>
<1059051709.3f1fd8bd630bc@stud.fbi.fh-darmstadt.de>
<3F1FF331.7000106@openratings.com>
<1059123702.3f20f1f689c61@stud.fbi.fh-darmstadt.de>
oheinz@stud.fbi.fh-darmstadt.de writes:
> Quoting Dmitry Tkach <dmitry@openratings.com>:
>> CREATE VIEW my_view AS SELECT b,c from
>> (SELECT a, b FROM table1 WHERE b=1) as my_ab,
>> (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
> I assume that with this statement postgresql will compute both subselects, do
> a cross join on both results an then reduce them to those who match the
> condition my_ac.a=my_ab.a, right?
No, it's smarter than that.
I tried the experiment in 7.3 and CVS tip, using some tables from the
regression database:
regression=# create view my_view as select b,c from
regression-# (select unique1,unique2 from tenk1 where unique2=1) as
regression-# my_ab(a,b),
regression-# (select unique1,unique2 from onek) as my_ac(a,c)
regression-# where my_ac.a = my_ab.a;
CREATE VIEW
regression=# explain select * from my_view;
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop (cost=0.00..24.47 rows=1 width=16)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..3.01 rows=1 width=8)
Index Cond: (unique2 = 1)
-> Index Scan using onek_unique1 on onek (cost=0.00..21.40 rows=5 width=8)
Index Cond: (onek.unique1 = "outer".unique1)
(5 rows)
regression=#
Looks like a fairly decent plan to me. It's certainly not letting the
sub-select structure get in its way.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, oheinz@stud.fbi.fh-darmstadt.de, dmitry@openratings.com, ch@rodos.fzk.de
Subject: Re: Problem using Subselect results
In-Reply-To: <8076.1059140715@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox