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

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@operamail.com)

potevo chiedere come si chiama il vostro cane
il mio è un po' di tempo che si chiama Libero

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