>> SELECT * FROM ab LEFT OUTER JOIN (bc JOIN cd ON bc.c = cd.d) ON ab.b = bc.b >> WHERE ab.a = 20000 >> As ab.a = 20000 occurs only once in ab one would expect that it just does an >> index scan on bc for ab.b = bc.b. > > The only way it could do that would be by interchanging the order of the > left and inner joins, ie (ab left join bc) join cd; which would change > the results.
In theory, I believe this could be rewritten as: SELECT * FROM ab LEFT OUTER JOIN (SELECT bc.b FROM ab JOIN bc ON ab.b = bc.b JOIN cd ON bc.c = cd.d WHERE ab.b = 20000) dummy ON ab.b = dummy.b WHERE ab.a = 20000 ...without affecting the results. If the condition ab.a = 20000 is highly selective, this is a big win. I can predict that Tom will say that the planning time it would take to avoid this problem isn't justified by the number of queries that it would improve. That's possible, but it's unfortunate that there's no way to fiddle with the knobs and get the planner to do this kind of thing when you want it to. Rewriting the query as described above is OK when you're writing the whole query from scratch, but I don't know of an easy fix for this: CREATE VIEW xyz AS SELECT * FROM ab LEFT OUTER JOIN (bc JOIN cd ON bc.c = cd.d) ON ab.b = bc.b Sometimes I want to SELECT * FROM xyz ORDER BY a LIMIT 100 (to let the user browse records) and sometimes I want to SELECT * FROM WHERE a = 20000 (retrieve a single record). Neither query performs acceptably if the planner generates the entire cross-product of bc and cd and then throws most of it away, unless bc and cd are very small tables. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
