Thread: 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@uvadrev.udmnet.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@uvadrev.udmnet.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@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
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