On 08/28/2017 09:31 AM, Don Seiler wrote:
> I think what I'm failing to understand is how it got so big without an
> autoanalyze kicking in at some point to keep relatively in sync. To my
> knowledge, autovacuum was never disabled here (I'm new, so I can't say
> with any certainty that it never was). I would expect as the inserted
> rows grow past a certain threshold, it would kick in an occasional
> analyze so it wouldn't get over 2 orders of magnitude off from 170K to 71M.
I can't say without knowing the history of this table and your server.
Autoanalyze with default settings should kick in at 0.1 (10%) of the
table changing. Lets say your table exists with 71 million rows. Then at
some point your server lost power. The info in the stats collector gets
reset on recovery. Now you modify 170k rows. That is now the change that
autoanalyze will base its decision on. It is looking for 0.1 x 70
million rows to change which is 7 million. You currently only have 170k
that have changed.
Bottom line is as I said earlier -- the default values for autovac and
autoanalyze scaling factor are ridiculously high for a table this large.
Do a manual VACUUM ANALYZE on it, and modify the settings for this table.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development