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.
