Re: Limiting the number of parameterized indexpaths created

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Limiting the number of parameterized indexpaths created
Date: 2012-11-05 19:44:16
Message-ID: 23123.1352144656@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

> 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.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2012-11-05 19:46:35 Re: Limiting the number of parameterized indexpaths created
Previous Message Robert Haas 2012-11-05 19:09:43 Re: Limiting the number of parameterized indexpaths created