From: Alessandro Depase <alessandro.depase@libero.it>
To: pgsql-sql@postgresql.org
Subject: failed to build any 5-way joins
Date: Tue, 16 Dec 2003 12:46:49 +0100
Message-ID: <001e01c3c3ca$4c0c4af0$0200a8c0@dedalus1> (raw)
Hi all,
I got the error message reported in the subject and I could not find anything about it anywhere.
I cannot get any explain plan for this query.
I'm working on PosgreSQL 7.4-1, the latest cygwin distribution I can find.
The query generating the error is:
select *
from info where parent_infoid is null
and fieldtypeid in (select fieldtypeid from users_auth_groups uag1, field_auth fa where userid = 8 and fa.groupid = uag1.groupid)
and main_infoid in (select ic.infoid from info_category ic, category_auth ca, users_auth_groups uag2
where ic.categoryid = ca.categoryid and uag2.userid = 8 and uag2.groupid = ca.groupid and read_write = 'W')
I already have a workaround, but I don't like it because it seems to me that it could be a little slower (executing the exists clause for every possible record got from the rest of the query, according to the explain plan results - and if I understood them well :) ):
select *
from info where parent_infoid is null
and exists (select * from users_auth_groups uag1, field_auth fa where userid = 8 and fa.groupid = uag1.groupid and info.fieldtypeid = fa.fieldtypeid)
and main_infoid in (select ic.infoid from info_category ic, category_auth ca, users_auth_groups uag2
where ic.categoryid = ca.categoryid and uag2.userid = 8 and uag2.groupid = ca.groupid and read_write = 'W')
This seems to work, however (and I will keep it until I will find another solution - well, I know that, for example, I can do it using stored procedure, splitting the query in more steps, but this is not my first choice, at the moment, for reasons too long to explain here).
The problem is: does anyone know what sort of error did I get and/or where can I find docs about it?
I suppose (just from the error message) that the optimizer is trying to translate the first query in a join and it gets an error doing so. But, is this error related to some limits in PostgreSQL? Are there limits in joining tables? I could not find anything about this too (I just found a my-sql crash-me test report in which they tell that PostgreSQL - v. 7.1.1 - passed the test with 64+ tables).
Thanks to you all for you help
Bye
Alessandro Depase
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alessandro.depase@libero.it
Subject: Re: failed to build any 5-way joins
In-Reply-To: <001e01c3c3ca$4c0c4af0$0200a8c0@dedalus1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox