Re: BETWEEN optimizer problems with single-value

On Thu, 2006-03-16 at 10:57 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)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