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
In-reply-to: <443E66F5.2010100@crpa.it> 
References: <443E66F5.2010100@crpa.it>
Comments: In-reply-to Cristian Veronesi <c.veronesi@crpa.it>
	message dated "Thu, 13 Apr 2006 16:57:57 +0200"
Date: Thu, 13 Apr 2006 11:50:08 -0400
Message-ID: <20558.1144943408@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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