>>>> The table has 6800 rows over 18000 pages, and is getting a
>>>> minimum of many tens of thousands of updates per day with
>>>> queries like this:
>>> If you're updating that much, how often are you running
>>> 'analyze'? Are you running autovacuum? How often?
>> I count on the built-in autovacuum to do do analyzes (per
>> 8.1.2 docs). I'm running autovacuum with the following
>> non-default parameters:
>>
>> autovacuum = on
>> autovacuum_naptime = 600 # 10 minutes
>
> I also have these non-default settings:
>
> autovacuum_vacuum_cost_delay = 500
> autovacuum_vacuum_cost_limit = 200
Not totally sure, but it sounds like the table isn't getting vacuumed
often enough. To help, you might reduce the naptime from 10 minutes to
5. But I think the cost_delay settings might be the larger problem. If
I remember correctly, even small values here tend to greatly increase
the time it takes vacuum commands to complete, so you might try backing
down those settings. Can you tell from the log files how often
autovacuum is actually taking actions, or how long those actions are taking?
Does anyone out there have any empirical data on good
autovacuum_*_cost_* settings? I would be curious to hear about them.