Re: BETWEEN optimizer problems with single-value

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. 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 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.

regards, tom lane