Odd optimiser behaviour
От | Christopher Kings-Lynne |
---|---|
Тема | Odd optimiser behaviour |
Дата | |
Msg-id | 0c8d01c298cc$19e39210$6500a8c0@internal обсуждение исходный текст |
Ответы |
Re: Odd optimiser behaviour
Re: Odd optimiser behaviour |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: