Skip site navigation (1) Skip section navigation (2)

Re: BUG #4926: too few pathkeys for mergeclauses

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Roman Kononov <kononov(at)ftml(dot)net>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4926: too few pathkeys for mergeclauses
Date: 2009-07-17 07:50:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Greg Stark wrote:
> On Thu, Jul 16, 2009 at 9:07 PM, Roman Kononov<kononov(at)ftml(dot)net> wrote:
>> test=# create table junk(i int);
>> test=# select * from junk left outer join (select coalesce(i,1) as x,
>> coalesce(i,2) as y from junk) t on coalesce(i,3)=x and coalesce(i,4)=y and
>> coalesce(i,5)=x;
>> ERROR:  too few pathkeys for mergeclauses
> Thanks for the bug report. That's definitely not supposed to be
> happening. It's always nice when it's easy to reproduce the problem
> like this.

Yep. This can be further reduced into this:

CREATE TABLE a (i integer);
CREATE TABLE b (x integer, y integer);

select * from a left outer join b on i=x and i=y and i=x;

The planner is choosing a merge join, where the outer side (table a) is
sorted by (i), and the inner side is sorted by (x, y). But that doesn't
work with the merge condition (i=x AND i=y AND i=x).

Version 8.3 has the same bug, apparently introduced along with the
equivalence classes. In 8.2, the merge condition is reduced into (i=x
AND i=y), IOW the planner eliminates the duplicate condition. I believe
8.2 would otherwise have the same problem as well.

I can see two different things that you could say is at fault here:

1. We no longer eliminate the duplicate condition, but the
find_mergeclauses_for_pathkeys() + make_inner_pathkeys_for_merge()
combination relies on there being no duplicates. We should try harder to
eliminate duplicates in left join clauses.

2. make_inner_pathkeys_for_merge() should have created sort order (x, y,
x) for the inner side.

The first solution is what we probably want, to avoid unnecessary work
at execution time.

  Heikki Linnakangas

In response to


pgsql-bugs by date

Next:From: Jan-Ivar MellingenDate: 2009-07-17 08:12:41
Subject: Huge speed penalty using <>TRUE instead of =FALSE
Previous:From: Alvaro HerreraDate: 2009-07-17 00:17:59
Subject: Re: Bug 4906?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group