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

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