Re: Type casting and indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: David Olbersen <DOlbersen(at)stbernard(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Type casting and indexes
Date: 2003-05-08 22:35:14
Message-ID: 17013.1052433314@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-05-09 12:31:46 Re: An unresolved performance problem.
Previous Message Tom Lane 2003-05-08 16:25:07 Re: Type casting and indexes