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: 4A602D35.3080008@enterprisedb.com (view raw or flat)
Thread:
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);
>> CREATE TABLE
>> 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
  EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

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-2014 The PostgreSQL Global Development Group