Re: Definitive answer: can functions use indexes?

On 1/6/16 5:41 PM, Tom Lane wrote:
> 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.)

BTW, the case where this would be highly valuable is timestamps. Being
able to do something like date_part('month',timestamptz)='Jan' would be
a big, big deal for warehousing.
--
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