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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux