Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> 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
