Re: BUG #3865: ERROR: failed to build any 8-way joins

The following bug has been logged online:

Bug reference: 3865
Logged by: Oleg Kharin
Email address: ok(at)uvadrev(dot)udmnet(dot)ru
PostgreSQL version: 8.2.6
Operating system: CentOS 5.1 x86 64-bit
Description: ERROR: failed to build any 8-way joins
Details:

After PostgreSQL 8.2.5 to 8.2.6 upgrade there is a query that generates:
ERROR: failed to build any 8-way joins.

The text of the query is rather big. It will be better to attach its text as
a file as well as the SQL script that creates a test case. But I don't know
how to post a file in the bug report form.

Oleg

Oleg Kharin wrote:
> The following bug has been logged online:
>
> Bug reference: 3865
> Logged by: Oleg Kharin
> Email address: ok(at)uvadrev(dot)udmnet(dot)ru
> PostgreSQL version: 8.2.6
> Operating system: CentOS 5.1 x86 64-bit
> Description: ERROR: failed to build any 8-way joins
> Details:
>
> After PostgreSQL 8.2.5 to 8.2.6 upgrade there is a query that generates:
> ERROR: failed to build any 8-way joins.
>
> The text of the query is rather big. It will be better to attach its text as
> a file as well as the SQL script that creates a test case. But I don't know
> how to post a file in the bug report form.

you can simply reply to this mail and attach a testcase or post it on a
website for download - but we will definitly need a testcase for that ...

Stefan

[RESENDING because the attachments seems to have caused the mail to
disappear]

Oleg Kharin wrote:
> Hi, Stefan

Hi Oleg!

I have readded the list and the testcases to the CC so that others can
participate in the discussion too ...

>
> init826.sql builds necessary tables and indexes. The test query is in
> the file query826.sql.

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)

Stefan

Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)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

I wrote:
> 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.

8.2 patch is here, if you need it now:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php

regards, tom lane

> 8.2 patch is here, if you need it now:
> http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php

Thanks. I have applied this patch and everything works fine.

Regards,
Oleg