pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfailed to build any 5-way joins
8+ messages / 4 participants
[nested] [flat]
* failed to build any 5-way joins
@ 2003-12-16 11:46 Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 13:30 ` Re: failed to build any 5-way joins Peter Eisentraut <peter_e@gmx.net>
2003-12-16 14:22 ` Re: failed to build any 5-way joins Tom Lane <tgl@sss.pgh.pa.us>
2003-12-17 15:32 ` Re: failed to build any 5-way joins Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 8+ messages in thread
From: Alessandro Depase @ 2003-12-16 11:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: failed to build any 5-way joins
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
@ 2003-12-16 13:30 ` Peter Eisentraut <peter_e@gmx.net>
2003-12-16 15:34 ` Re: failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2003-12-16 13:30 UTC (permalink / raw)
To: Alessandro Depase <alessandro.depase@libero.it>; pgsql-sql
Alessandro Depase wrote:
> The query generating the error is:
This is useless unless we know table schema, what data is in the tables,
and what software version you use.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: failed to build any 5-way joins
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 13:30 ` Re: failed to build any 5-way joins Peter Eisentraut <peter_e@gmx.net>
@ 2003-12-16 15:34 ` Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 16:12 ` Re: failed to build any 5-way joins Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Alessandro Depase @ 2003-12-16 15:34 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
Peter Eisentraut wrote:
> > The query generating the error is:
>
> This is useless unless we know table schema, what data is in the tables,
> and what software version you use.
I can just give you (at the end of this answer) the DDL for the fields I
reported in the query, but they are enough because I tried the example I'm
giving you (with all tables empty and without FK, while in the original
database they had less than 50 rows each) and I got the same result (so, it
should be data independent - but I understand that you meant that the error
could be data type dependent, so I give you the - reduced - tables
definitions).
I expected that the error was data independent, because, as I told before, I
could not get the plan for the not-working query.
Version? In my previous message I told that I'm using 7.4-1 on cygwin
distribution. What other info do you need? How can I get them?
All I can think more than this is that I use a Windows XP O.S. and that I
got this error both using JDBC and using pgAdmin III (but it seems to me
that this is an error client-independent).
The other problem, however, as I asked before, is: where can I find the
documentation for this error (well, besides the source, of course)? are
there known limits in joining tables?
Thanks again
Bye
Alessandro Depase
CREATE TABLE public.info
(
infoid varchar(10) NOT NULL,
parent_infoid varchar(10),
main_infoid varchar(10),
fieldtypeid varchar(10) NOT NULL,
CONSTRAINT info_pkey PRIMARY KEY (infoid)
) WITH OIDS;
CREATE TABLE public.users_auth_groups
(
userid varchar(10) NOT NULL,
groupid varchar(10) NOT NULL,
CONSTRAINT users_auth_groups_pkey PRIMARY KEY (userid, groupid)
) WITH OIDS;
CREATE TABLE public.field_auth
(
groupid varchar(10) NOT NULL,
fieldtypeid varchar(10) NOT NULL,
read_write varchar(1) NOT NULL,
CONSTRAINT field_auth_pkey PRIMARY KEY (groupid, fieldtypeid)
) WITH OIDS;
CREATE TABLE public.category_auth
(
groupid varchar(10) NOT NULL,
categoryid varchar(10) NOT NULL,
read_write varchar(1),
CONSTRAINT category_auth_pkey PRIMARY KEY (groupid, categoryid)
) WITH OIDS;
CREATE TABLE public.info_category
(
infoid varchar(10) NOT NULL,
categoryid varchar(10) NOT NULL,
CONSTRAINT info_category_pkey PRIMARY KEY (infoid, categoryid)
) WITH OIDS;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: failed to build any 5-way joins
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 13:30 ` Re: failed to build any 5-way joins Peter Eisentraut <peter_e@gmx.net>
2003-12-16 15:34 ` Re: failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
@ 2003-12-16 16:12 ` Richard Huxton <dev@archonet.com>
2003-12-16 16:23 ` Re: failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2003-12-16 16:12 UTC (permalink / raw)
To: Alessandro Depase <alessandro.depase@libero.it>; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
On Tuesday 16 December 2003 15:34, Alessandro Depase wrote:
>
People smarter than me are looking at this, but I can give you some quick
feedback.
> Version? In my previous message I told that I'm using 7.4-1 on cygwin
> distribution. What other info do you need? How can I get them?
Are you sure it's 7.4.1? I wasn't aware that was publicly released yet.
> All I can think more than this is that I use a Windows XP O.S. and that I
> got this error both using JDBC and using pgAdmin III (but it seems to me
> that this is an error client-independent).
It certainly looks like a bug in PG itself, rather than client-related. I'd
agree with your guess that it's somewhere in the query rewriter.
> The other problem, however, as I asked before, is: where can I find the
> documentation for this error (well, besides the source, of course)? are
> there known limits in joining tables?
I'm not aware of any particular limits on joins. There's even a genetic
optimiser that kicks in when joins go above a certain number of tables
(default of 12 but configurable in 7.4 iirc?).
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: failed to build any 5-way joins
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 13:30 ` Re: failed to build any 5-way joins Peter Eisentraut <peter_e@gmx.net>
2003-12-16 15:34 ` Re: failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 16:12 ` Re: failed to build any 5-way joins Richard Huxton <dev@archonet.com>
@ 2003-12-16 16:23 ` Alessandro Depase <alessandro.depase@libero.it>
0 siblings, 0 replies; 8+ messages in thread
From: Alessandro Depase @ 2003-12-16 16:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
The version declared by the cygwin setup is 7.4-1 (I don't know if the '-1'
is the same that your '.1' or, for example, a progressive in the cygwin
PostgreSQL build sequence).
A 'select version()' answers with this information:
PostgreSQL 7.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.3.1 (cygming
special)
Are there other ways to get a better insight?
Alessandro Depase
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Alessandro Depase" <alessandro.depase@libero.it>; "Peter Eisentraut"
<peter_e@gmx.net>; <pgsql-sql@postgresql.org>
Sent: Tuesday, December 16, 2003 5:12 PM
Subject: Re: [SQL] failed to build any 5-way joins
> On Tuesday 16 December 2003 15:34, Alessandro Depase wrote:
> >
> People smarter than me are looking at this, but I can give you some quick
> feedback.
> > Version? In my previous message I told that I'm using 7.4-1 on cygwin
> > distribution. What other info do you need? How can I get them?
>
> Are you sure it's 7.4.1? I wasn't aware that was publicly released yet.
>
> > All I can think more than this is that I use a Windows XP O.S. and that
I
> > got this error both using JDBC and using pgAdmin III (but it seems to me
> > that this is an error client-independent).
>
> It certainly looks like a bug in PG itself, rather than client-related.
I'd
> agree with your guess that it's somewhere in the query rewriter.
>
> > The other problem, however, as I asked before, is: where can I find the
> > documentation for this error (well, besides the source, of course)? are
> > there known limits in joining tables?
>
> I'm not aware of any particular limits on joins. There's even a genetic
> optimiser that kicks in when joins go above a certain number of tables
> (default of 12 but configurable in 7.4 iirc?).
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: failed to build any 5-way joins
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
@ 2003-12-16 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2003-12-16 14:22 UTC (permalink / raw)
To: Alessandro Depase <alessandro.depase@libero.it>; +Cc: pgsql-sql
"Alessandro Depase" <alessandro.depase@libero.it> writes:
> I got the error message reported in the subject and I could not find anythi=
> ng about it anywhere.
It's a bug, but we can't do much about it unless you provide a
reproducible example. In addition to the query itself, schemas
for the tables involved would be needed (use pg_dump -s). Test
that you can load the schema dump into an empty database, execute
the problem query, and get the failure.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: failed to build any 5-way joins
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
@ 2003-12-17 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-17 16:04 ` Re: failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-12-17 15:32 UTC (permalink / raw)
To: Alessandro Depase <alessandro.depase@libero.it>; +Cc: pgsql-sql
"Alessandro Depase" <alessandro.depase@libero.it> writes:
> select *
> from info where parent_infoid is null=20
> and fieldtypeid in (select fieldtypeid from users_auth_groups uag1, field_a=
> uth fa where userid =3D 8 and fa.groupid =3D uag1.groupid)=20
> and main_infoid in (select ic.infoid from info_category ic, category_auth c=
> a, users_auth_groups uag2=20
> where ic.categoryid =3D ca.categoryid and uag2.userid =3D 8 and uag2.grou=
> pid =3D ca.groupid and read_write =3D 'W')=20
Hmm. It's right, there's no way to construct a sub-plan that joins just
that number of relations, because of the constraint that the IN
sub-selects have to be fully formed before we can do IN processing.
A brute force solution is to just remove the error cross-check in
src/backend/optimizer/path/joinrels.c:
if (result_rels == NIL)
elog(ERROR, "failed to build any %d-way joins", level);
I'll probably install some less-drastic fix for 7.4.1, but if you need a
solution right now, that will get you going.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: failed to build any 5-way joins
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2003-12-17 15:32 ` Re: failed to build any 5-way joins Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-17 16:04 ` Alessandro Depase <alessandro.depase@libero.it>
0 siblings, 0 replies; 8+ messages in thread
From: Alessandro Depase @ 2003-12-17 16:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thanks Tom, but I think I can wait, being possible to use the exists clause
and not having, at the moment a big database.
When there will be the less-drastic fix you refer to, I will use it and
change my query.
Thanks again.
Bye
Alessandro Depase
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Alessandro Depase" <alessandro.depase@libero.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, December 17, 2003 4:32 PM
Subject: Re: [SQL] failed to build any 5-way joins
> "Alessandro Depase" <alessandro.depase@libero.it> writes:
> > select *
> > from info where parent_infoid is null=20
> > and fieldtypeid in (select fieldtypeid from users_auth_groups uag1,
field_a=
> > uth fa where userid =3D 8 and fa.groupid =3D uag1.groupid)=20
> > and main_infoid in (select ic.infoid from info_category ic,
category_auth c=
> > a, users_auth_groups uag2=20
> > where ic.categoryid =3D ca.categoryid and uag2.userid =3D 8 and
uag2.grou=
> > pid =3D ca.groupid and read_write =3D 'W')=20
>
> Hmm. It's right, there's no way to construct a sub-plan that joins just
> that number of relations, because of the constraint that the IN
> sub-selects have to be fully formed before we can do IN processing.
>
> A brute force solution is to just remove the error cross-check in
> src/backend/optimizer/path/joinrels.c:
>
> if (result_rels == NIL)
> elog(ERROR, "failed to build any %d-way joins", level);
>
> I'll probably install some less-drastic fix for 7.4.1, but if you need a
> solution right now, that will get you going.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-12-17 16:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-16 11:46 failed to build any 5-way joins Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 13:30 ` Peter Eisentraut <peter_e@gmx.net>
2003-12-16 15:34 ` Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 16:12 ` Richard Huxton <dev@archonet.com>
2003-12-16 16:23 ` Alessandro Depase <alessandro.depase@libero.it>
2003-12-16 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-17 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-17 16:04 ` Alessandro Depase <alessandro.depase@libero.it>
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