Re: Definitive answer: can functions use indexes?

> On 07 Jan 2016, at 5:19, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote:
>
> 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.

Not just warehousing, for BI in general.

But, as is now quite clear, for many of those cases it should be fairly trivial to work around this limitation by creating either a functional index or an operator. For the above example, say something like timestamptz % 'month' = 'Jan'.

There are downsides to that approach though, such as readability and that this way of using % instead of date_part() is not according to any standard behaviour and could even behave differently or (more likely) not work at all on other PG instances.

That said, it's not uncommon in BI to require a seq. scan anyway, in which case the point is rather moot.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.