On Thu, Mar 02, 2006 at 11:19:32AM -0500, Chaz. wrote: > I am trying to understand something I have seen happen. I had a select > that looked like: > > select f(A) from A, B, C where g(A) > > Where f(A) is the select that only depends on table A; > g(A) is the where part that only depends on table A. > > What I saw happen was the optimizer will waste a lot of time (seconds!) > bringing in table B and C. I was wondering why doesn't the optimizer > drop references to tables B and C since they aren't used any where?
The above query does a cross join. Even though you're not using values from B and C they're still contributing rows to the result set. test=> SELECT * FROM a; aid ----- a1 a2 (2 rows) test=> SELECT * FROM b; bid ----- b1 b2 (2 rows) test=> SELECT * FROM c; cid ----- c1 c2 (2 rows) test=> SELECT a.*, b.*, c.* FROM a, b, c WHERE a.aid = 'a1'; aid | bid | cid -----+-----+----- a1 | b1 | c1 a1 | b2 | c1 a1 | b1 | c2 a1 | b2 | c2 (4 rows) test=> SELECT a.*, b.* FROM a, b, c WHERE a.aid = 'a1'; aid | bid -----+----- a1 | b1 a1 | b2 a1 | b1 a1 | b2 (4 rows) test=> SELECT a.* FROM a, b, c WHERE a.aid = 'a1'; aid ----- a1 a1 a1 a1 (4 rows) -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
