Seamus Abshere <seamus@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
