Re: index is not used if I include a function that returns current time in my query

Cristian Veronesi <c(dot)veronesi(at)crpa(dot)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