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 19:28:18 +0000
Message-ID: <1142537298.3859.497.camel@localhost.localdomain> (raw)
In-Reply-To: <17043.1142524621@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>
<1142510032.3859.320.camel@localhost.localdomain>
<17043.1142524621@sss.pgh.pa.us>
On Thu, 2006-03-16 at 10:57 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > 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.
>
> Where would that be?
>
> > The operator and the opclass are only connected via an index access
> > method, but for a particular index each column has only one opclass.
>
> If you're proposing making clauselist_selectivity depend on what indexes
> exist, I think that's very much the wrong approach.
Using available information sounds OK to me. Guess you're thinking of
the lack of plan invalidation?
> In the first place,
> it still has to give usable answers for unindexed columns, and in the
> second place there might be multiple indexes with different opclasses
> for the same column, so the ambiguity problem still exists.
I was thinking that we would fill out the OpExpr with different
opclasses for each plan, so each one sees a different story. (I was
thinking there was a clauselist for each plan; if not, there could be.)
So the multiple index problem shouldn't exist.
Non-indexed cases still cause the problem, true.
> I have been wondering if we shouldn't add some more indexes on pg_amop
> or something to make it easier to do this sort of lookup --- we
> definitely seem to be finding multiple reasons to want to look up
> which opclasses contain a given operator.
Agreed, but still looking for better way than that.
[BTW how do you add new indexes to system tables? I want to add one to
pg_inherits but not sure where to look.]
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: <1142537298.3859.497.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