Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group