Re: [HACKERS] Odd optimiser behaviour

[Joe Conway]

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]