From: Tom Lane <tgl@sss.pgh.pa.us>
To: tim_wilson <tim.wilson@telogis.com>
cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum vacuum creates bad statistics for planner when it log
 index scans: 0
In-reply-to: <1400459729388-5804416.post@n5.nabble.com>
References: <1400459729388-5804416.post@n5.nabble.com>
Comments: In-reply-to tim_wilson <tim.wilson@telogis.com>
 message dated "Sun, 18 May 2014 17:35:29 -0700"
Date: Tue, 20 May 2014 00:13:22 -0400
Message-ID: <11299.1400559202@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

tim_wilson <tim.wilson@telogis.com> writes:
> On a 9.3.1 server , I have a key busy_table in that is hit by most
> transactions running on our system. One DB's copy of this table has 60K rows
> and 1/3 of that tables rows can updated every minute.

> Autovacuum autovacuum_analyze_scale_factor is set 0.02, so that analyse runs
> nearly every minute. But when autovacuum vacuum runs I sometimes see the
> following message in logs:

> LOG:  automatic vacuum of table "busy_table":* index scans: 0*
>         pages: 0 removed, 22152 remain
>         tuples: 0 removed, 196927 remain
>         buffer usage: 46241 hits, 478 misses, 715 dirtied
>         avg read rate: 0.561 MB/s, avg write rate: 0.839 MB/s
>         system usage: CPU 0.07s/0.06u sec elapsed 6.66 sec

> and the tuples remaining is then overestimated by a factor >3 , and have
> seen this over estimate as large at >20 times IE 5M

FWIW, I tried to reproduce this without success.

There's some code in there that attempts to extrapolate the total number
of live tuples when VACUUM has not scanned the entire table.  It's surely
plausible that that logic went off the rails ... but without a test case
or at least a more specific description of the problem scenario, it's
hard to know what's wrong exactly.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance