Thread: index is not used if I include a function that returns current time in my query
index is not used if I include a function that returns current time in my query
From
Cristian Veronesi
Date:
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.
Re: index is not used if I include a function that returns current time in my query
From
Tom Lane
Date:
Cristian Veronesi <c.veronesi@crpa.it> writes: > If I try to use a function that returns the current time instead, a > sequential scan is always performed: > ... > Any suggestion? 1. Use something newer than 7.4 ;-) 2. Set up a dummy range constraint, ie select ... where ora_rif > localtimestamp and ora_rif < 'infinity'; The problem you have is that the planner doesn't know the value of the function and falls back to a default assumption about the selectivity of the '>' condition --- and that default discourages indexscans. (Note the very large estimate of number of rows returned.) In the range-constraint situation, the planner still doesn't know the value of the function, but its default assumption for a range constraint is tighter and it (probably) will choose an indexscan. Since PG 8.0, the planner understands that it's reasonable to pre-evaluate certain functions like localtimestamp to obtain better-than-guess values about selectivity, so updating would be a better fix. regards, tom lane