Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > I can confirm that this works on 8.2.5 but fails: > * on -HEAD > * on 8.2.6 (so we have a rather bad regression)
> with a join_collaps_limit > 3 and < 10 - you could increase that on your > box but it is likely that other queries are affected in a different way > so it might not help at all. > the testcase from oleg is available on: > http://www.kaltenbrunner.cc/files/init826.sql (DDL) > http://www.kaltenbrunner.cc/files/query826.sql (Query) I looked into this and found that it's caused by this patch: http://archives.postgresql.org/pgsql-committers/2007-10/msg00409.php The problem is that have_join_order_restriction() concludes that we don't need to force clauseless joins for certain combinations of relations because it sees that there are available join clauses for one or both relations. However, in this example (with join_collapse_limit less than 10) this is happening inside a subset of the total collection of relations, and the join clauses it's seeing link to relations that are outside the current subset. The only way to form a plan for the subset is to do at least one clauseless join, but the code doesn't explore that path and so fails to form a valid plan. The most expedient way to fix it seems to be to make has_legal_joinclause consider, not the set of all relations anywhere in the query, but just members of the current initial_rels list. There's a notational problem, which is that that's currently a local in make_rel_from_joinlist() and not accessible from anywhere near has_legal_joinclause. We could add a field to PlannerInfo to pass it down; but I find that a tad ugly :-( Now in a situation where this is happening, we *know* we are generating a pretty crummy plan by forcing the clauseless join --- if we'd been operating with a higher collapse_limit we would have found another plan not requiring a clauseless join. So a nicer fix would be to somehow modify the "joinlist" subdivision of the planning problem so that we don't get backed into this type of corner. I don't currently see any way to do that though --- at the point where we're setting up the joinlists, the information is theoretically available but discovering there's going to be a problem seems to require duplicating a lot of the subsequent planning work. In the meantime, the suggestion to raise join_collapse_limit is probably a reasonable workaround for the OP. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
