Skip site navigation (1) Skip section navigation (2)

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

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Cris Carampa <cris119(at)operamail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: index is not used if I include a function that returns current time in my query
Date: 2006-04-18 19:56:01
Message-ID: 20060418195601.GE49405@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
Interesting.... what's EXPLAIN ANALYZE show if you SET
enable_seqscan=off; ?

You should also consider upgrading to 8.1...

On Thu, Apr 13, 2006 at 12:25:02PM +0200, Cris Carampa wrote:
> 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,
> 
> -- 
> Cris Carampa (spamto:cris119(at)operamail(dot)com)
> 
> potevo chiedere come si chiama il vostro cane
> il mio ? un po' di tempo che si chiama Libero
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-04-18 20:01:15
Subject: Re: Blocks read for index scans
Previous:From: Tom LaneDate: 2006-04-18 19:51:44
Subject: Re: Slow query - possible bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group