Re: query planner does not canonicalize infix operators

Daniel Farina <daniel(at)heroku(dot)com> writes:
> On Mon, Mar 12, 2012 at 7:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Will Leinweber <will(at)heroku(dot)com> writes:
>>> I created an index on an hstore function, fetchval(hstore, text), however
>>> when I use the -> infix operator which resolves to the very same function,
>>> this index is not used. It should be used.

>> Don't hold your breath. Create an index on the expression you intend to
>> use, not random respellings of it.

> Is this saying "there no need for that" or "no one is working on it,
> and I certainly don't intend to", or "definitely not in the next
> version" or something else entirely?

The reason this is difficult is that the bulk of the planner's
optimization knowledge is attached to operators, not functions. It'd be
easy enough to smash operators down to their underlying functions during
expression preprocessing, sure. But then we would fail to recognize index
applicability at all --- for instance an index on an integer column can
get matched to "indexcol = 42", but not to "int4eq(indexcol, 42)". And
we'd have little clue about the selectivity of the expression, either,
since selectivity estimators are attached to operators not functions.

Arguably the Berkeley guys got this wrong 25 years ago, and they should
have defined indexes and selectivity in terms of functions not
operators. However I really don't foresee us reinventing all the
"operator class" infrastructure to make that happen; the amount of work
required is far out of proportion to the benefit, even without
considering the number of external projects that would get impacted.
(Inventing selectivity estimators for functions is a less daunting task,
and we might do that sometime ... but I think it would likely live
alongside operator selectivity rather than replace it.)

More generally, I'm not prepared to buy into the idea that the planner
should be expected to recognize alternate spellings of "the same"
expression. There are too many variants of that idea that are
infeasible either because the planner doesn't have the necessary
knowledge, or it does but trying to recognize the equivalence would cost
an impractical number of planning cycles. Neither of those objections
apply to "replace an operator by its underlying function"; but they do
apply to other comparable requests we've gotten such as "recognize that
x + 0 is the same as x" or "x + 1 is the same as 1 + x".

regards, tom lane