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
