Re: Definitive answer: can functions use indexes?

Seamus Abshere <seamus(at)abshere(dot)net> writes:
> I've been using Postgres for years ( :heart: ) and I'm still in doubt
> about this. Would somebody provide an authoritative, definitive,
> narrative answer?

> -> Can a function like `LEFT()` use an index?

To do what?

Since the question makes little sense as stated, I'm going to assume
you mean "can a query like SELECT ... WHERE left(foo, 3) = 'bar'
use an index on column foo?"

The answer to that is no, there is no such optimization built into
Postgres. (In principle there could be, but I've not heard enough
requests to make me think we'd ever pursue it.)

The equivalent optimization that *is* built in, and has been for
a long time, is for LIKE: "SELECT ... WHERE foo LIKE 'bar%'" can
use an index on foo, at least if it's an index sorted according to
C collation.

Another answer, which might serve as long as your application only
cares about a small number of prefix lengths, is functional indexes.
If you create a functional index on "left(foo,3)" you're all set.
This won't scale well to a whole bunch of different lengths, though.

regards, tom lane