Thread: Odd optimiser behaviour

I'm seeing this:

I have indexed the suspended_off column.

usa=# explain analyze UPDATE users_users SET suspended=false,
suspended_on=NULL, suspended_off=NULL WHERE suspended_off='2002-02-02';
NOTICE:  QUERY PLAN:

Index Scan using users_users_susp_off_idx on users_users
(cost=0.00..1005.91 rows=266 width=248) (actual time=0.36..0.36 rows=0
loops=1)
Total runtime: 0.60 msec

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..2922.14 rows=266 width=248) (actual
time=237.38..237.38 rows=0 loops=1)
Total runtime: 237.60 msec

EXPLAIN

Why does using CURRENT_DATE instead of a literal date make a difference?

So then I tried using a partial index, since 99% of the rows will have NULL
values in suspended_off:

usa=# create index users_users_susp_off_idx on users_users(suspended_off)
where suspended_off is not null;
CREATE
usa=# explain analyze UPDATE users_users SET suspended=false,
suspended_on=NULL, suspended_off=NULL WHERE suspended_off='2002-02-02';
NOTICE:  QUERY PLAN:

Seq Scan on users_users  (cost=0.00..2793.55 rows=267 width=248) (actual
time=301.51..301.51 rows=0 loops=1)
Total runtime: 301.90 msec

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.

Chris

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

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Why does using CURRENT_DATE instead of a literal date make a difference?

In 7.3 it doesn't.  Prior versions do not understand that CURRENT_DATE
can be treated like a constant in this context.  Time to update ;-)
        regards, tom lane