Re: Berserk Autovacuum (let's save next Mandrill)

On Mon, Mar 16, 2020 at 07:47:13AM -0500, Justin Pryzby wrote:
> Normally, when someone complains about bad plan related to no index-onlyscan,
> we tell them to run vacuum, and if that helps, then ALTER TABLE .. SET
> (autovacuum_vacuum_scale_factor=0.005).
>
> If there's two thresholds (4 GUCs and 4 relopts) for autovacuum, then do we
> have to help determine which one was being hit, and which relopt to set?

I don't think we came to any resolution on this.

Right now, to encourage IOS, we'd tell someone to set
autovacuum_vacuum_scale_factor=0.005. That wouldn't work for an insert-only
table, but I've never heard back from someone that it didn't work.

So with this patch, we'd maybe tell them to do this, to also get IOS on
insert-only tables ?
|ALTER TABLE .. SET (autovacuum_vacuum_scale_factor=0.005, autovacuum_vacuum_insert_threshold=50000);

> I wonder if the new insert GUCs should default to -1 (disabled)? And the
> insert thresholds should be set by new insert relopt (if set), or by new insert
> GUC (default -1), else normal relopt, or normal GUC. The defaults would give
> 50 + 0.20*n. When someone asks about IOS, we'd tell them to set
> autovacuum_vacuum_scale_factor=0.005, same as now.
>
> vac_ins_scale_factor =
> (relopts && relopts->vacuum_ins_scale_factor >= 0) ? relopts->vacuum_ins_scale_factor :
> autovacuum_vac_ins_scale >= 0 ? autovacuum_vac_ins_scale :
> (relopts && relopts->vacuum_scale_factor >= 0) ? relopts->vacuum_scale_factor :
> autovacuum_vac_scale;

--
Justin