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

Re: Limiting the number of parameterized indexpaths created

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Limiting the number of parameterized indexpaths created
Date: 2012-11-05 19:46:35
Message-ID: CA+TgmobsBUY70esj-FRJ2QEd=UeFZo7vM4boekhW5RCXs5tzcw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Nov 5, 2012 at 2:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Nov 5, 2012 at 2:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> There are three different parameterized paths we could create: one
>>> relying on x only, one relying on y only, one relying on both.
>
>> Sure, but that example is different from the test case provided in the
>> bug report.  I agree that here we need to try paths parameterized by
>> a, b, or both a and b.  Things might blow up multiplicatively, because
>> we have join clauses referencing both t.a and t.b.  But they shouldn't
>> blow up exponentially, because each of t.a and t.b can only be
>> parameterized by ONE thing (I think).
>
> Um, no.  This is a useful counterexample:
>
>         WHERE t.a > x.c1 AND t.a < y.c2
>
> With a range constraint like this one, it's possible for the
> doubly-parameterized path to be quite useful while either
> singly-parameterized path is basically useless.  And these examples
> aren't even going into cases you might get with non-btree indexes,
> where clauses could interact in much more complicated ways.

Well, OK.  So maybe you also need the operator to be the same as well.

>> And in the example in the bug
>> report, only one column of the table (foo.id) is mentioned.  foo.id
>> can be driven by ag1.aid OR ag2.aid OR ag3.aid OR ..., but not more
>> than one of those at a time.
>
> In the example, we do figure out that the clauses are redundant, but
> only further downstream.  The code that's got the problem can't really
> assume such a thing.  As patched, it will indeed limit what it considers
> to at most one additional clause per index column, once it's hit the
> heuristic limit --- but it's entirely possible for it to miss useful
> combinations because of that.

Seems unfortunate, but I don't understand the code well enough to know
how to do better.


-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


In response to

Responses

pgsql-hackers by date

Next:From: Daniel FarinaDate: 2012-11-05 19:59:04
Subject: Re: Synchronous commit not... synchronous?
Previous:From: Tom LaneDate: 2012-11-05 19:44:16
Subject: Re: Limiting the number of parameterized indexpaths created

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