PostgreSQL forgets about its index

Question

Hello!

Periodically, Postgres "forgets" about its index and starts to slow down. I do analyze and it "recalls" back its index.

Everything would be ok, but last time this happened only after 2 hours I did analyze. There are no deletes, inserts are quite slow, around 10000 per hour. Might be this a bug in Postgresql itself? Version 8.3.7

trio=# explain Select p.id, p.status
trio-# From m_a a
trio-# Join m_b p On a.m_id = p.id
trio-# Where a.user_id = ? And a.tx = ?
trio-# Order By a.id Desc
trio-# Limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3335.25 rows=1 width=18)
  -> Nested Loop (cost=0.00..673719.63 rows=202 width=18)
    -> Index Scan Backward using m_a_pkey on m_a a (cost=0.00..671058.45 rows=202 width=8)
       Filter: ((user_id = ?) AND (tx = ?::numeric))
    -> Index Scan using m_b_pkey on m_b p (cost=0.00..13.16 rows=1 width=14)
       Index Cond: (p.id = a.m_id)
(6 rows)

trio=# analyze m_a;
ANALYZE
trio=# explain Select p.id, p.status
trio-# From m_a a
trio-# Join m_b p On a.m_id = p.id
trio-# Where a.user_id = ? And a.tx = ?
trio-# Order By a.id Desc
trio-# Limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=23.18..23.18 rows=1 width=18)
  -> Sort (cost=23.18..23.18 rows=1 width=18)
     Sort Key: a.id
    -> Nested Loop (cost=0.00..23.17 rows=1 width=18)
      -> Index Scan using m_a_idx on m_a a (cost=0.00..9.99 rows=1 width=8)
         Index Cond: ((user_id = ?) AND (tx = ?::numeric))
      -> Index Scan using m_b_pkey on m_b p (cost=0.00..13.16 rows=1 width=14)
         Index Cond: (p.id = a.m_id)
(8 rows)

===========

Hello depesz, here is analize output. Avtovacuum is on.

#default_statistics_target = 10  # range 1-1000




#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on    # Enable autovacuum subprocess?  'on' 
        # requires track_counts to also be on.
#log_autovacuum_min_duration = -1   # -1 disables, 0 logs all actions and
        # their durations, > 0 logs only
        # actions running at least that time.
#autovacuum_max_workers = 3  # max number of autovacuum subprocesses
#autovacuum_naptime = 1min   # time between autovacuum runs
#autovacuum_vacuum_threshold = 50   # min number of row updates before
        # vacuum
#autovacuum_analyze_threshold = 50  # min number of row updates before 
        # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
        # (change requires restart)
#autovacuum_vacuum_cost_delay = 20  # default vacuum cost delay for
        # autovacuum, -1 means use
        # vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1  # default vacuum cost limit for
        # autovacuum, -1 means use
        # vacuum_cost_limit

==========

       QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22.07..22.07 rows=1 width=18) (actual time=0.308..0.309 rows=1 loops=1)
   ->  Sort  (cost=22.07..22.07 rows=1 width=18) (actual time=0.306..0.306 rows=1 loops=1)
         Sort Key: a.id
         Sort Method:  quicksort  Memory: 25kB
         ->  Nested Loop  (cost=0.00..22.06 rows=1 width=18) (actual time=0.277..0.280 rows=1 loops=1)
               ->  Index Scan using m_a_uidx on m_a a  (cost=0.00..8.76 rows=1 width=8) (actual time=0.162..0.162 rows=1 loops=1)
                     Index Cond: ((user_id = ?) AND (tx = ?::numeric))
               ->  Index Scan using m_b_pkey on m_b p  (cost=0.00..13.29 rows=1 width=14) (actual time=0.109..0.111 rows=1 loops=1)
                     Index Cond: (p.id = a.m_id)
 Total runtime: 0.397 ms
(10 rows)

Answer 1

1. explain output is pretty useless - show us explain analyze output (and please use formatting tags to keep it readable) - for both queries - slow and fast
2. why don't you have autovacuum on?
3. what is your default_statistics_target?

Answer 2

Hmmm sounds like auto-vacuum might have gone weary, you might want to check the relevant logs and dig a bit in that direction.