Re: 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]

 



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@xxxxxxxxxxxxx)
> 
> 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@xxxxxxxxxxxxx
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


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

  Powered by Linux