Skip site navigation (1) Skip section navigation (2)

Re: Odd optimiser behaviour

From: Joe Conway <mail(at)joeconway(dot)com>
To: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Odd optimiser behaviour
Date: 2002-12-01 00:20:48
Message-ID: 3DE955E0.9090702@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Christopher Kings-Lynne wrote:
> EXPLAIN
> usa=# explain analyze UPDATE users_users SET suspended=false,
> suspended_on=NULL, suspended_off=NULL WHERE suspended_off=CURRENT_DATE;
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on users_users  (cost=0.00..2927.26 rows=267 width=248) (actual
> time=466.76..466.76 rows=0 loops=1)
> Total runtime: 467.02 msec
> 
> EXPLAIN
> 
> And now I'm always getting sequential scans.  What gives?  I analyze the
> table between runs.
> 

In gram.y I see that CURRENT_DATE is transformed to 'now'::text::date. Here's 
the comment:

* We cannot use "'now'::date" because coerce_type() will
* immediately reduce that to a constant representing
* today's date.  We need to delay the conversion until
* runtime, else the wrong things will happen when
* CURRENT_DATE is used in a column default value or rule.

So I'm guessing that the optimizer sees this as volatile and therefore not 
something it can use an index for. Try using now()::date instead, or maybe 
wrap the call to CURRENT_DATE in a function of your own and mark it stable.

Joe


In response to

pgsql-hackers by date

Next:From: David WheelerDate: 2002-12-01 00:25:57
Subject: Re: 7.4 Wishlist
Previous:From: Joe ConwayDate: 2002-12-01 00:14:12
Subject: Re: 7.4 Wishlist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group