> On 07 Jan 2016, at 5:19, Jim Nasby <jim.na...@bluetreble.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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
