Hi, After pondering on the problem for quite some time and discussing it on IRC with RhodiumToad I thought the most sensible thing is to post the problem here (as RhodiumToad suggested as well).
The original (although already quite reduced) problematic query and the related plan: http://anarazel.de/postgres/orig_query.sql http://anarazel.de/postgres/orig_query.plan I.e. it builds the right side of the LEFT JOIN for all elements it could possibly contain and not only for the ones which exist on the left side. (Database is freshly VACUUM ANALYZE'd) Perhaps I expect to much from the planner here? With this query this is not much of a problem, but the plan is the same if the inner part of the query yields some million rows (and possibly is not only In order to make testing easier I tried to reproduce the problem (with help of RhodiumToad): http://anarazel.de/postgres/create_testtables.sql Testquery: 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. Unfortunately it builds the complete right side of the join first, and then selects the one element it needs... Queryplan: http://anarazel.de/postgres/testtable_query1.plan If there is no relatively easy fix for this, any idea how to work around that problem? Thanks, Andres Freund PS: Tested with 8.3.3 and 8.2.7. The problem was the same since 8.0 though (I didn't test earlier versions )
signature.asc
Description: This is a digitally signed message part.
