Обсуждение: index is not used if I include a function that returns current time in my query

Hello, postgresql 7.4.8 on SuSE Linux here.

I have a table called DMO with a column called ORA_RIF defined as
"timestamp without time zone" ;

I created an index on this table based on this column only.

If I run a query against a text literal the index is used:

> explain select * from dmo where ora_rif>'2006-01-01';
                                   QUERY PLAN
-----------------------------------------------------------------------------------------
  Index Scan using dmo_ndx02 on dmo  (cost=0.00..1183.23 rows=736 width=156)
    Index Cond: (ora_rif > '2006-01-01 00:00:00'::timestamp without time
zone)

If I try to use a function that returns the current time instead, a
sequential scan is always performed:

> explain select * from dmo where ora_rif>localtimestamp;
                                   QUERY PLAN
------------------------------------------------------------------------------
  Seq Scan on dmo  (cost=0.00..1008253.22 rows=2703928 width=156)
    Filter: (ora_rif > ('now'::text)::timestamp(6) without time zone)

> explain select * from dmo where ora_rif>localtimestamp::timestamp
without time zone;
                                   QUERY PLAN
------------------------------------------------------------------------------
  Seq Scan on dmo  (cost=0.00..1008253.22 rows=2703928 width=156)
    Filter: (ora_rif > ('now'::text)::timestamp(6) without time zone)

... etc. ...

(tried with all datetime functions with and without cast)

I even tried to write a function that explicitly returns a "timestamp
without time zone" value:

create or replace function f () returns timestamp without time zone
as '
declare
   x timestamp without time zone ;
begin
   x := ''2006-01-01 00:00:00'';
   return x ;
end ;
' language plpgsql ;

But the result is the same:

> explain select * from dmo ora_rif>f();
                                  QUERY PLAN
-----------------------------------------------------------------------------
  Seq Scan on dmo  (cost=0.00..987973.76 rows=2703928 width=156)
    Filter: (ora_rif > f())

Any suggestion?

Kind regards,

--
Cristian Veronesi - C.R.P.A. S.p.A. - Reggio Emilia, Italy

The first thing you need to learn about databases is that
they are not just a fancy file system for storing data.

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