Re: Type casting and indexes

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> On Thu, 8 May 2003, David Olbersen wrote:
>> So it seems that the type conversion is killing the use of the index,
>> even though the type conversion has to happen for the condition to be
>> tested.

> IIRC, timestamp->timestamptz is not considered to give a constant value
> (ie, is not stable)

No: it is stable, but not immutable, because it depends on SET TIMEZONE.
(Our policy on those is if you change one mid-query, it's unspecified
whether the query will notice or not.) So the query is potentially
indexable.

The problem here is that instead of seeing a constant, the planner sees
a nonconstant function invocation on the right side of '>', and so it
has to fall back to a default selectivity estimate instead of being able
to extract a reasonable estimate from pg_statistic. The default
estimate is high enough to discourage an indexscan ...

regards, tom lane