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