From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [HACKERS] BETWEEN optimizer problems with single-value
Date: Wed, 15 Mar 2006 17:34:33 -0600
Message-ID: <44185029.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1142463908.3859.188.camel@localhost.localdomain>
References: <44180105.EE98.0025.0@wicourts.gov>
<1142463908.3859.188.camel@localhost.localdomain>
>>> On Wed, Mar 15, 2006 at 5:05 pm, in message
<camel@localhost.localdomain/">1142463908.3859.188.camel@localhost.localdomain>, Simon Riggs
<simon@2ndquadrant.com> wrote:
> On Wed, 2006- 03- 15 at 11:56 - 0600, Kevin Grittner wrote:
>
>> (One obvious way to fix it would be to
>> rewrite "BETWEEN a AND b" as "= a" when a is equal to b, but it
seems
>> like there is some underlying problem which should be fixed instead
(or
>> in addition to) this.
>
> That might work, but I'm not sure if that is in itself the problem
and
> it would be mostly wasted overhead in 99% of cases.
It sounds like we agree.
> The main issue appears to be that the planner chooses "Cal_CalDate"
> index rather than "Cal_CtofcNo" index when the BETWEEN values match.
Agreed.
> It seems that the cost of the first and third EXPLAINs is equal, yet
for
> some reason it chooses different indexes in each case. My
understanding
> was that it would pick the first index created if plan costs were
equal.
> Is that behaviour repeatable with each query?
It seems to be a consistent pattern, although strictly speaking our
evidence is anecdotal. We've got hundreds of known failures with the
BETWEEN variant on equal dates and no known successes. We have a few
dozen tests of the equality variant with 100% success in those tests.
> ISTM that if we have equal plan costs then we should be choosing the
> index for which we have more leading columns, since that is more
likely
> to lead to a more selective answer. But the plan selection is a
simple
> "pick the best, or if they're equal pick the best sort order".
> The selectivity seems the same in both - clamped to a minimum of 1
row,
> so changing that doesn't look like it would help.
The fact that it costs these as equivalent is surprising in itself, and
might be worth examining. This might be an example of something I
suggested a while ago -- that the rounding a row estimate to an integer
on the basis that "you can't read half a row" is not necessarily wise,
because you can have a 50% chance of reading a row versus a higher or
lower percentage.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, simon@2ndquadrant.com
Subject: Re: [HACKERS] BETWEEN optimizer problems with single-value
In-Reply-To: <44185029.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox