Subject: Re: BETWEEN optimizer problems with single-value
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org,
	Andreas Kretschmer <akretschmer@spamfence.net>
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>
Content-Type: text/plain
Date: Thu, 16 Mar 2006 19:28:18 +0000
Message-Id: <1142537298.3859.497.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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