From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Seamus Abshere <seamus(at)abshere(dot)net>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Definitive answer: can functions use indexes? |
Date: | 2016-01-06 23:35:06 |
Message-ID: | 568DA4AA.3040103@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2016-01-06 23:35:51 | Re: Code of Conduct: Is it time? |
Previous Message | Seamus Abshere | 2016-01-06 23:15:23 | Definitive answer: can functions use indexes? |