Re: Problem using Subselect results

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