Given this table
articles_1=> \d hits_user_daily_count;
Table "public.hits_user_daily_count"
Column | Type | Modifiers
---------------------------+---------+-----------
userid | integer | not null
date | date | not null
num_hits | integer | default 0
num_cover_page_hits | integer | default 0
num_additional_files_hits | integer | default 0
Indexes:
"hits_user_daily_count_pkey" PRIMARY KEY, btree (userid, date)
"hits_user_daily_count_date" btree (date)
whose data looks like this:
articles_1=> select * from hits_user_daily_count limit 5;
userid | date | num_hits | num_cover_page_hits |
num_additional_files_hits
---------+------------+----------+---------------------+---------------------------
1590185 | 2013-07-10 | 3 | 4 |
0
391907 | 2013-07-10 | 16 | 12 |
0
1739541 | 2013-08-03 | 1 | 0 |
0
1798435 | 2013-07-10 | 0 | 1 |
0
1521468 | 2013-07-10 | 2 | 0 |
0
We noticed a big change after upgrading from 9.0 to 9.2. For
*yesterday‘s*date, the query plan is fine, like you’d expect
articles_1=> explain (analyze, buffers) select 1
from hits_user_daily_count
where userid = 1590185 and date = '2013-07-30';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using hits_user_daily_count_pkey on
hits_user_daily_count (cost=0.00..8.02 rows=1 width=0) (actual
time=0.02
Index Cond: ((userid = 1590185) AND (date = '2013-07-30'::date))
Heap Fetches: 1
Buffers: shared hit=5
Total runtime: 0.044 ms
but for *today‘s* date it looks like it’s reading all the rows for that
date, 15,277 buffers:
articles_1=> explain (analyze, buffers) select 1
from hits_user_daily_count
where userid = 1590185 and date = '2013-08-01';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using hits_user_daily_count_date on hits_user_daily_count
(cost=0.00..7.92 rows=1 width=0) (actual time=11.957..1
Index Cond: (date = '2013-08-01'::date)
Filter: (userid = 1590185)
Rows Removed by Filter: 20149
Buffers: shared hit=15277
Total runtime: 17.924 ms
(The dates in the queries are old because I've had this email in draft for
a while, but the behavior is still identical).
We‘ve addressed the problem by running ’analyze' on the table every day ate
about 1:30am. Buffer hits on that table go from about 1,000/sec to
70,000/sec between midnight and that analyze job, and then go back down to
1,000/sec and stay flat until midnight rolls around again.
Before the 9.0 -> 9.2 upgrade, the behavior would be flat all day.
Any ideas what would be causing that problem?
