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