From: Cristian Veronesi <c.veronesi@crpa.it>
To: pgsql-performance@postgresql.org
Subject: index is not used if I include a function that returns current time in my query
Date: Thu, 13 Apr 2006 16:57:57 +0200
Message-ID: <443E66F5.2010100@crpa.it> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: c.veronesi@crpa.it
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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox