Re: Problem using Subselect results

oheinz(at)stud(dot)fbi(dot)fh-darmstadt(dot)de writes:
> Quoting Dmitry Tkach <dmitry(at)openratings(dot)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