Thread: could not devise a query plan
Dear Gurus,
I couldn't find the string of my email's subject on the web, except for one
place: the PostgreSQL source :)
So I'm desperate.
--
VERSION
I'm using "PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4"
with the patch for "shown aggregate columns is 0" (if you know what I mean
;) )
Version "PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4" on a
differend machine yields the same results, except as noted below.
Difference may be the version or something else, but there is a recent
mirror of the 7.3.3 db (generated from textual pg_dump) on the 7.4.1 server
that also throws the error.
--
ABSTRACT
#1. Below is a very simplified query that throws this error. The original
query used a view, CASE's, aggregates, function calls and meaningful WHERE
clauses :) The idea is to join the table with itself, but the subselects sum
different rows in field vi_m and sz_m.
Some modifications solve the problem, I show two versions.
#2. One is a single field rename (counts much in NATURAL FULL),
#3. the other is a group by construction.
There is another erroneous query:
#4. Giving an outer WHERE clause to #3, the error is back, BUT ONLY IN 7.4.1
--
DETAILS
are at the end of this email.
--
CONCLUSION
If this is enough to give me a clue, I'd be grateful.
If there is a general discussion about this error, I'd be honoured.
If you'd like to see the original query and corresponding definitions, I
think I can share it with you.
If this is a bug and has been fixed since 7.4.1, I'd take the task to
compile a newer version and see how it fares.
G.
%----------------------- cut here -----------------------%
\end
\d sztgy
Table "pg_temp_4.sztgy" Column | Type | Modifiers
---------------------+---------------+-----------az | integer |allapot | integer
|megrendelo | integer |szallito | integer |keretrendeles_az | integer
|teljesites | date |szallitolevel_fajta | integer |szallitas | integer
|tetelszam | integer |cikk | integer |minoseg | integer
|mennyiseg | numeric(14,4) |fajta | integer |mennyisegi_egyseg | integer
|hibastatusz | integer |
%----------------------- cut here -----------------------%
-- #1: This throws the error:
SELECT * FROM
(SELECT * FROM(SELECT sum(mennyiseg) as vi_m FROM sztgy) szt_having
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM(SELECT sum(mennyiseg) as sz_m FROM sztgy) vsz_having
) AS vsz;
ERROR: could not devise a query plan for the given query
%----------------------- cut here -----------------------%
-- #2: This works, with a single rename, but useless for me:
SELECT * FROM
(SELECT * FROM(SELECT sum(mennyiseg) as sz_m FROM sztgy) szt_having
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM(SELECT sum(mennyiseg) as sz_m FROM sztgy) vsz_having
) AS vsz; sz_m
----------------530515336.8900
(1 row)
%----------------------- cut here -----------------------%
-- #3: This works, with group-by
-- the original query has group-by clause, but throws the error (see #4)
-- SELECT'ed count just to show the result. SELECT'ing * also works.
SELECT count(*) FROM
(SELECT * FROM(SELECT cikk, minoseg, sum(mennyiseg) as vi_m FROM sztgy group by cikk, minoseg) szt_having
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM(SELECT cikk, minoseg, sum(mennyiseg) as sz_m FROM sztgy group by cikk, minoseg) vsz_having
) AS vsz;
count
------- 1590
(1 row)
%----------------------- cut here -----------------------%
-- #4: This works only on server v7.3.3:
SELECT * FROM
(SELECT * FROM(SELECT cikk, minoseg, sum(mennyiseg) as vi_m FROM sztgy group by cikk, minoseg) szt_havingwhere
cikk=101917and minoseg=1
) AS szt
NATURAL FULL OUTER JOIN
(SELECT * FROM(SELECT cikk, minoseg, sum(mennyiseg) as sz_m FROM sztgy group by cikk, minoseg) vsz_havingwhere
cikk=101917and minoseg=1
) AS vsz;
-- 7.3.3: cikk | minoseg | vi_m | sz_m
--------+---------+---------+---------101917 | 1 | 20.0000 | 20.0000
(1 row)
-- 7.4.1:
ERROR: could not devise a query plan for the given query
"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> If this is a bug and has been fixed since 7.4.1, I'd take the task to
> compile a newer version and see how it fares.
It's still there in CVS tip :-(. Will look into it today.
regards, tom lane
"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> I couldn't find the string of my email's subject on the web, except for one
> place: the PostgreSQL source :)
Seems that you've managed to tickle a strange corner case, which can be
reduced to simplest form like this:
regression=# select * from a full join b on true;
ERROR: Unable to devise a query plan for the given query
In your first example, the empty join clause falls out because you don't
actually have any columns of the same names on both sides, and so the
NATURAL join doesn't find any columns to join. AFAICS this is allowed
by the SQL spec, but still I can't help suspecting that it is
programming error on your part. You're going to get a cross-product
join ... is that really what you intended?
The second example is slightly more interesting: it boils down to a case
like this:
select * from (select unique1 from tenk1 where unique1 = 42) afull join (select unique1 from tenk1 where unique1 = 42)
bona.unique1 = b.unique1;
7.4 is perhaps too smart for its own good here: it is able to figure out
that the join clause is redundant because every row coming up from the
subselects must have the same value in the join columns (here, 42). So
it discards the join clause ... leaving it in the same situation where
it can't generate a plan :-(
Although I think the first case is really user error, the second case
looks like it could arise unexpectedly in program-generated queries
given the right combination of inputs, so we probably ought to do
something about it. I have applied the attached patch to 7.4.
(It would probably work in 7.3 too, but no guarantees.)
regards, tom lane
Index: costsize.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/costsize.c,v
retrieving revision 1.115.2.1
diff -c -r1.115.2.1 costsize.c
*** costsize.c 3 Dec 2003 17:45:36 -0000 1.115.2.1
--- costsize.c 6 Apr 2004 18:41:17 -0000
***************
*** 928,950 **** * all mergejoin paths associated with the merge clause, we cache the * results in the
RestrictInfonode. */
! firstclause = (RestrictInfo *) lfirst(mergeclauses);
! if (firstclause->left_mergescansel < 0) /* not computed yet? */
! mergejoinscansel(root, (Node *) firstclause->clause,
! &firstclause->left_mergescansel,
! &firstclause->right_mergescansel);
!
! if (bms_is_subset(firstclause->left_relids, outer_path->parent->relids)) {
! /* left side of clause is outer */
! outerscansel = firstclause->left_mergescansel;
! innerscansel = firstclause->right_mergescansel; } else {
! /* left side of clause is inner */
! outerscansel = firstclause->right_mergescansel;
! innerscansel = firstclause->left_mergescansel; } /* convert selectivity to row count; must scan at
leastone row */
--- 928,958 ---- * all mergejoin paths associated with the merge clause, we cache the * results in the
RestrictInfonode. */
! if (mergeclauses) {
! firstclause = (RestrictInfo *) lfirst(mergeclauses);
! if (firstclause->left_mergescansel < 0) /* not computed yet? */
! mergejoinscansel(root, (Node *) firstclause->clause,
! &firstclause->left_mergescansel,
! &firstclause->right_mergescansel);
!
! if (bms_is_subset(firstclause->left_relids, outer_path->parent->relids))
! {
! /* left side of clause is outer */
! outerscansel = firstclause->left_mergescansel;
! innerscansel = firstclause->right_mergescansel;
! }
! else
! {
! /* left side of clause is inner */
! outerscansel = firstclause->right_mergescansel;
! innerscansel = firstclause->left_mergescansel;
! } } else {
! /* cope with clauseless mergejoin */
! outerscansel = innerscansel = 1.0; } /* convert selectivity to row count; must scan at least one row
*/
Index: joinpath.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.82
diff -c -r1.82 joinpath.c
*** joinpath.c 25 Sep 2003 06:58:00 -0000 1.82
--- joinpath.c 6 Apr 2004 18:41:18 -0000
***************
*** 489,497 **** outerpath->pathkeys,
mergeclause_list);
! /* Done with this outer path if no chance for a mergejoin */ if (mergeclauses == NIL)
! continue; if (useallclauses && length(mergeclauses) != length(mergeclause_list))
continue;
--- 489,515 ---- outerpath->pathkeys,
mergeclause_list);
! /*
! * Done with this outer path if no chance for a mergejoin.
! *
! * Special corner case: for "x FULL JOIN y ON true", there will be
! * no join clauses at all. Ordinarily we'd generate a clauseless
! * nestloop path, but since mergejoin is our only join type that
! * supports FULL JOIN, it's necessary to generate a clauseless
! * mergejoin path instead.
! *
! * Unfortunately this can't easily be extended to handle the case
! * where there are joinclauses but none of them use mergejoinable
! * operators; nodeMergejoin.c can only do a full join correctly if
! * all the joinclauses are mergeclauses.
! */ if (mergeclauses == NIL)
! {
! if (jointype == JOIN_FULL && restrictlist == NIL)
! /* okay to try for mergejoin */ ;
! else
! continue;
! } if (useallclauses && length(mergeclauses) != length(mergeclause_list)) continue;
Dear Tom, I didn't get your replies, but found them in the archives. Thanks a lot, the patched 7.4 works with the original query! (didn't patch 7.3 since it's a production db, and it works in 7.3 anyway) Thanks again, Yours, G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "SZŰCS Gábor" <surrano@mailbox.hu> Sent: Tuesday, April 06, 2004 3:12 PM
Dear Gurus, Sorry for upping a 13-month-old thread; please tell if I should've opened another one. Here I come again, with another silly join. Please forgive me, but our queries are built from blocks :) VERSION: 7.4.6, 7.4.8, 8.0.0rc4 (sorry, no newer installed right now) ABSTRACT: The following query fails. SELECT * FROM (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa NATURAL FULL JOIN (SELECT a,b FROM cdqp WHERE a=1 and b=2)AS bbWHERE a+b = 3; DETAILS: It seems it's something about the redundant WHERE clauses. If I comment (replace with "WHERE true") any of the three WHERE clauses, it works. Session log: See below. TIA, -- G. serv1:tir=> \d cdqp Table "pg_temp_20.cdqp" Column | Type | Modifiers --------+---------+----------- a | integer | b | integer | serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE a+b = 3; ERROR: could not devise a query plan for the given query serv1:tir=> SELECT * FROM serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa serv1:tir-> NATURAL FULL JOIN serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb serv1:tir-> WHERE true; a | b ---+--- 1 | 2 (1 row)
Dear Gnanavel, (please reply to the lists...) Indeed it works! Still, I think it's a bug. As for this solution being a workaround, it's a bit of pain, since the subselect names (effectively, the included subselects) are not constant. As for my workaround, I used a condition to not include the last WHERE clause in the query when the inner WHERE clauses are present (since it does nothing). -- G. On 2005.05.24. 13:14, Gnanavel Shanmugam wrote: > replace > WHERE a+b = 3; > with > WHERE aa.a+aa.b = 3; > > (or) > WHERE bb.a+bb.b = 3; > > > with regards, > S.Gnanavel > > > >>-----Original Message----- >>From: surrano@gmail.com >>Sent: Tue, 24 May 2005 12:39:04 +0200 >>To: pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org >>Subject: Re: [SQL] could not devise a query plan >> >>Dear Gurus, >> >>Sorry for upping a 13-month-old thread; please tell if I should've opened >>another one. >> >>Here I come again, with another silly join. Please forgive me, but our >>queries are built from blocks :) >> >>VERSION: 7.4.6, 7.4.8, 8.0.0rc4 (sorry, no newer installed right now) >> >>ABSTRACT: The following query fails. >> >> SELECT * FROM >> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa >> NATURAL FULL JOIN >> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb >> WHERE a+b = 3; >> >>DETAILS: It seems it's something about the redundant WHERE clauses. If I >>comment (replace with "WHERE true") any of the three WHERE clauses, it >>works. >> >>Session log: See below. >> >>TIA, >> >>-- >>G. >> >> >>serv1:tir=> \d cdqp >> Table "pg_temp_20.cdqp" >> Column | Type | Modifiers >>--------+---------+----------- >> a | integer | >> b | integer | >> >>serv1:tir=> SELECT * FROM >>serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa >>serv1:tir-> NATURAL FULL JOIN >>serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb >>serv1:tir-> WHERE a+b = 3; >>ERROR: could not devise a query plan for the given query >>serv1:tir=> SELECT * FROM >>serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa >>serv1:tir-> NATURAL FULL JOIN >>serv1:tir-> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb >>serv1:tir-> WHERE true; >> a | b >>---+--- >> 1 | 2 >>(1 row) >> >>---------------------------(end of broadcast)--------------------------- >>TIP 9: the planner will ignore your desire to choose an index scan if >>your >> joining column's datatypes do not match
Szűcs Gábor <surrano@gmail.com> writes:
> ABSTRACT: The following query fails.
> SELECT * FROM
> (SELECT a,b FROM cdqp WHERE a=1 AND b=2) AS aa
> NATURAL FULL JOIN
> (SELECT a,b FROM cdqp WHERE a=1 and b=2) AS bb
> WHERE a+b = 3;
Thanks for the report! Seems I overlooked a case when fixing the
original report last year. Patch for 8.0 attached (it's the same in
7.4 too).
regards, tom lane
Index: joinpath.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.91.4.1
diff -c -r1.91.4.1 joinpath.c
*** joinpath.c 23 Jan 2005 02:22:27 -0000 1.91.4.1
--- joinpath.c 24 May 2005 17:54:15 -0000
***************
*** 498,512 **** * nestloop path, but since mergejoin is our only join type that * supports FULL
JOIN,it's necessary to generate a clauseless * mergejoin path instead.
- *
- * Unfortunately this can't easily be extended to handle the case
- * where there are joinclauses but none of them use mergejoinable
- * operators; nodeMergejoin.c can only do a full join correctly if
- * all the joinclauses are mergeclauses. */ if (mergeclauses == NIL) {
! if (jointype == JOIN_FULL && restrictlist == NIL) /* okay to try for mergejoin */ ;
else continue;
--- 498,507 ---- * nestloop path, but since mergejoin is our only join type that * supports FULL
JOIN,it's necessary to generate a clauseless * mergejoin path instead. */ if (mergeclauses ==
NIL) {
! if (jointype == JOIN_FULL) /* okay to try for mergejoin */ ; else
continue;
This tip was at the end of a message (from Szűcs Gábor). > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match it looks very important, but I cannot understand it. Sound as a small and easy mistake that can make things go sour... Can someone explain it please? thanks, Alain
On Tue, 2005-05-24 at 13:26, Alain wrote: > This tip was at the end of a message (from Szűcs Gábor). > > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > it looks very important, but I cannot understand it. Sound as a small > and easy mistake that can make things go sour... > > Can someone explain it please? Note that this is mostly fixed in V 8.0 What it means is that if you have a table with a field of type numeric, and you join another table against it with a field of type integer, the query planner won't know it can use the indexes on those two fields (assuming you DO have indexes on them) when joining and use a sequential scan all the time. Casting one field to the other's type, or changing the type of one to the other's type will allow the planner to use the index.