Re: query planner does not canonicalize infix operators

On Mon, Mar 12, 2012 at 12:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

Argh. That is very sad. Given that an operator definitely resolves to
a function (right?) in the function catalog it seems a little insane.

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

I see. That is ugly.

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

I think that the case of infix-operator-to-function stands out in that
it doesn't really rely on knowing any algebriac properties of the
underlying function such as commutativity, associativity, the notion
of a nil-value in the algebra, et al. It's a bit concerning because
ORMs are starting to pick up on the extra data types in Postgres and
they'll have to all spell the "preferred" spelling of the
functionality to properly compose. If SQLAlchemy generates "fetchval"
and ActiveRecord 4 uses "->", tough times are ahead.

Thanks for your detailed response. As a small point of criticism, I
think your response to Will came off as a bit strong, even though
there are good reasons why this deceptively small request turns into a
snarl that may only be of interest to optimizer hackers.

On the more constructive side, if I were to till the fields to change
this aspect of the optimizer, is there any interest in rectifying the
operator-function confusion?

--
fdr