Re: Does auto-analyze work on dirty writes?

On 02/04/2011 10:41 AM, Tom Lane wrote:
> 1. Autovacuum fires when the stats collector's insert/update/delete
> counts have reached appropriate thresholds.  Those counts are
> accumulated from messages sent by backends at transaction commit or
> rollback, so they take no account of what's been done by transactions
> still in progress.
>
> 2. Only live rows are included in the stats computed by ANALYZE.
> (IIRC it uses SnapshotNow to decide whether rows are live.)
>
> Although the stats collector does track an estimate of the number of
> dead rows for the benefit of autovacuum, this isn't used by planning.
> Table bloat is accounted for only in terms of growth of the physical
> size of the table in blocks.

Thanks, Tom.

Does this un-analyzed "bloat" not impact queries? I guess the worst case 
here is if autovaccum is disabled for some reason and 99% of the table 
is dead rows. If I understand the above correctly, I think analyze might 
generate a bad plan under this scenario, thinking that a value is 
unique, using the index - but every tuple in the index has the same 
value and each has to be looked up in the table to see if it is visible?

Still, I guess the idea here is not to disable autovacuum, making dead 
rows insignificant in the grand scheme of things. I haven't specifically 
noticed any performance problems here - PostgreSQL is working great for 
me as usual. Just curiosity...

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>