From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org, Andreas Kretschmer <akretschmer@spamfence.net>
Subject: Re: BETWEEN optimizer problems with single-value
Date: Thu, 16 Mar 2006 11:53:52 +0000
Message-ID: <1142510032.3859.320.camel@localhost.localdomain> (raw)
In-Reply-To: <11428.1142474724@sss.pgh.pa.us>
References: <44180105.EE98.0025.0@wicourts.gov>
<20060315181735.GA22240@KanotixBox>
<44180D33.EE98.0025.0@wicourts.gov>
<28798.1142450270@sss.pgh.pa.us>
<1142467626.3859.207.camel@localhost.localdomain>
<1142468678.3859.221.camel@localhost.localdomain>
<11428.1142474724@sss.pgh.pa.us>
On Wed, 2006-03-15 at 21:05 -0500, Tom Lane wrote:
> So we need a more accurate estimate for the boundary case.
Agreed.
> > Using 1.0e-10 isn't very useful... the selectivity for a range should
> > never be less than the selectivity for an equality, so we should simply
> > put in a test against one of the pseudo constants and use that as the
> > minimal value.
>
> That's easier said than done, because you'd first have to find the
> appropriate equality operator to use (ie, one having semantics that
> agree with the inequality operators).
...
Kevin: this is also the reason we can't simply transform the WHERE
clause into a more appropriate form...
> Possibly we could drop this code's reliance on seeing
> SCALARLTSEL/SCALARGTSEL as the estimators, and instead try to locate a
> common btree opclass for the operators --- which would then let us
> identify the right equality operator to use, and also let us distinguish
> > from >= etc. If we're trying to get the boundary cases right I
> suspect we have to account for that. I could see such an approach being
> tremendously slow though :-(, because we'd go looking for btree
> opclasses even for operators that have nothing to do with < or >.
Trying to get the information in the wrong place would be very
expensive, I agree. But preparing that information when we have access
to it and passing it through the plan would be much cheaper. Relating
op->opclass will be very useful in other places in planning, even if any
one case seems not to justify the work to record it. (This case feels
like deja vu, all over again.)
The operator and the opclass are only connected via an index access
method, but for a particular index each column has only one opclass. So
the opclass will have a 1-1 correspondence with the operator for *that*
plan only, realising that other plans might have different
correspondences. find_usable_indexes() or thereabouts could annotate a
restriction OpExpr with the opclass it will use.
Once we have the link, clauselist_selectivity() can trivially compare
opclasses for both OpExprs, then retrieve other information for that
opclass for various purposes.
Seems lots of work for such a corner case, but would be worth it if this
solves other problems as well.
Best Regards, Simon Riggs
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: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, akretschmer@spamfence.net
Subject: Re: BETWEEN optimizer problems with single-value
In-Reply-To: <1142510032.3859.320.camel@localhost.localdomain>
* 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