Re: Definitive answer: can functions use indexes?

Seamus Abshere <seamus(at)abshere(dot)net> writes:
> I should have been more general. In layman's/narrative terms, what's the
> deal with functions vs. operators for postgres indexes?

> For example, `exist(hstore,text)` vs. `hstore ? text` ?

Yeah. exist(hstore,text) and hstore?text may yield the same result,
but only the latter is a candidate to be used with an index on an hstore
column. This is a consequence of decisions that were made twenty-five or
more years ago at Berkeley, to design the core system's interface to index
support in terms of operators and operator classes (there's a reason those
are not called "function classes"). At this point, those decisions are
so heavily embedded --- into not only the core code but perhaps hundreds
of third-party extensions --- that rethinking them would be very painful.
As long as the gain is only likely to be cosmetic, it probably won't
happen.

You can see some info about what I'm talking about here:
http://www.postgresql.org/docs/devel/static/xindex.html

A closely related issue is that most of the planner's optimization
intelligence is tied to operators, not functions, as shown here:
http://www.postgresql.org/docs/devel/static/xoper-optimization.html

Again, that's something that could be improved in principle, but
the amount of work involved seems disproportionate to the likely
benefit.

regards, tom lane