Re: optimizer behavior in the case of highly updated tables

On Wed, Jun 9, 2010 at 12:04 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Mark Rostron wrote:
>>
>> - It was necessary to take a site outage and perform a “vacuum full
>> analyze” on the table
>>
>> - Following this, the query plan reverted to the more efficient btree
>> lookup
>>
>> Clearly, the garbage buildup resulting from transaction activity on the
>> table is the villain here.
>>
>> - Is it possible to calculate expected space usage given row count and
>> average row size
>>
>> - At what point might the ratio of “expected”/”actual” space usage be able
>> to indicate the need to perform “full vacuum”, or similar maintenance
>>
>
> I think you're right to focus on this part, because with your usage
> pattern--deleting all old data constantly--you have to get this under
> control in order for the query planner to do the right thing here.

I think this is one of those places where you need to vacuum more
often and more aggresively to keep up. If the usage pattern works
well with truncating partitions then do that. But it's possible that
aggresive vacuuming can take care of this.

I wonder what vacuum verbose before and after the performance shift
would reveal anything useful about bloating.