pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex is not used if I include a function that returns current time in my query
4+ messages / 4 participants
[nested] [flat]
* index is not used if I include a function that returns current time in my query
@ 2006-04-13 10:25 Cris Carampa <cris119@operamail.com>
2006-04-18 19:56 ` Re: index is not used if I include a function that returns current time in my query Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 4+ messages in thread
From: Cris Carampa @ 2006-04-13 10:25 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index is not used if I include a function that returns current time in my query
2006-04-13 10:25 index is not used if I include a function that returns current time in my query Cris Carampa <cris119@operamail.com>
@ 2006-04-18 19:56 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2006-04-18 19:56 UTC (permalink / raw)
To: Cris Carampa <cris119@operamail.com>; +Cc: 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@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
^ permalink raw reply [nested|flat] 4+ messages in thread
* index is not used if I include a function that returns current time in my query
@ 2006-04-13 14:57 Cristian Veronesi <c.veronesi@crpa.it>
2006-04-13 15:50 ` Re: index is not used if I include a function that returns current time in my query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Cristian Veronesi @ 2006-04-13 14:57 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index is not used if I include a function that returns current time in my query
2006-04-13 14:57 index is not used if I include a function that returns current time in my query Cristian Veronesi <c.veronesi@crpa.it>
@ 2006-04-13 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-04-13 15:50 UTC (permalink / raw)
To: Cristian Veronesi <c.veronesi@crpa.it>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-04-18 19:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-13 10:25 index is not used if I include a function that returns current time in my query Cris Carampa <cris119@operamail.com>
2006-04-18 19:56 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-13 14:57 index is not used if I include a function that returns current time in my query Cristian Veronesi <c.veronesi@crpa.it>
2006-04-13 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox