simple query with radically different plan after 9.0 -> 9.2 upgrade - Mailing list pgsql-general

Given this table

articles_1=> \d hits_user_daily_count;     Table "public.hits_user_daily_count"         Column           |  Type   | Modifiers 
---------------------------+---------+-----------userid                    | integer | not nulldate                      | date    | not nullnum_hits                  | integer | default 0num_cover_page_hits       | integer | default 0num_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 |                         01739541 | 2013-08-03 |        1 |                   0 |                         01798435 | 2013-07-10 |        0 |                   1 |                         01521468 | 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=5Total 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=15277Total 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?