Re: Definitive answer: can functions use indexes?

On 1/6/16 5:15 PM, Seamus Abshere wrote:
> 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?
>
> (Or do I have to find an "equivalent" operator in order to leverage
> indexes?)

If you're looking for magic here, there is none.

CREATE INDEX ON a(field);
... WHERE field = LEFT(...) -- can use index
... WHERE LEFT(field) = ... -- can NOT use index

CREATE INDEX ON a(LEFT(field,5))
... WHERE field = LEFT(...) -- can NOT use index
... WHERE LEFT(field,5) = ... -- CAN use index
... WHERE LEFT(field,6) = ... -- can NOT use index
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com