Horton

Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Gould <daveg@sonic.net>
Cc: Jeff Janes <jeff.janes@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Alina Alexeeva <alexeeva@adobe.com>, Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Date: 2018-03-12T14:43:36Z
Lists: pgsql-hackers 
David Gould <daveg@sonic.net> writes:
> On Wed, 7 Mar 2018 21:39:08 -0800
> Jeff Janes <jeff.janes@gmail.com> wrote:
>> As for preventing it in the first place, based on your description of your
>> hardware and operations, I was going to say you need to increase the max
>> number of autovac workers, but then I remembered you from "Autovacuum slows
>> down with large numbers of tables. More workers makes it slower" (
>> https://www.postgresql.org/message-id/20151030133252.3033.4249%40wrigleys.postgresql.org).
>> So you are probably still suffering from that?  Your patch from then seemed
>> to be pretty invasive and so controversial.

> We have been building from source using that patch for the worker contention
> since then. It's very effective, there is no way we could have continued to
> rely on autovacuum without it. It's sort of a nuisance to keep updating it
> for each point release that touches autovacuum, but here we are.

Re-reading that thread, it seems like we should have applied Jeff's
initial trivial patch[1] (to not hold AutovacuumScheduleLock across
table_recheck_autovac) rather than waiting around for a super duper
improvement to get agreed on.  I'm a bit tempted to go do that;
if nothing else, it seems simple enough to back-patch, unlike most
of the rest of what was discussed.

			regards, tom lane

[1] https://www.postgresql.org/message-id/CAMkU%3D1zQUAV6Zv3O7R5BO8AfJO%2BLAw7satHYfd%2BV2t5MO3Bp4w%40mail.gmail.com

Commits

  1. Sync up our various ways of estimating pg_class.reltuples.

  2. Fix tuple counting in SP-GiST index build.

  3. Fix errors in contrib/bloom index build.

  4. When updating reltuples after ANALYZE, just extrapolate from our sample.

  5. Avoid holding AutovacuumScheduleLock while rechecking table statistics.