From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cristian Veronesi <c.veronesi@crpa.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: index is not used if I include a function that returns current time in my query
Date: Thu, 13 Apr 2006 11:50:08 -0400
Message-ID: <20558.1144943408@sss.pgh.pa.us> (raw)
In-Reply-To: <443E66F5.2010100@crpa.it>
References: <443E66F5.2010100@crpa.it>
Cristian Veronesi <c.veronesi@crpa.it> writes:
> If I try to use a function that returns the current time instead, a
> sequential scan is always performed:
> ...
> Any suggestion?
1. Use something newer than 7.4 ;-)
2. Set up a dummy range constraint, ie
select ... where ora_rif > localtimestamp and ora_rif < 'infinity';
The problem you have is that the planner doesn't know the value of the
function and falls back to a default assumption about the selectivity of
the '>' condition --- and that default discourages indexscans. (Note
the very large estimate of number of rows returned.) In the
range-constraint situation, the planner still doesn't know the value of
the function, but its default assumption for a range constraint is
tighter and it (probably) will choose an indexscan.
Since PG 8.0, the planner understands that it's reasonable to
pre-evaluate certain functions like localtimestamp to obtain
better-than-guess values about selectivity, so updating would
be a better fix.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, c.veronesi@crpa.it
Subject: Re: index is not used if I include a function that returns current time in my query
In-Reply-To: <20558.1144943408@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox