Sender: gc@mnc.ch
From: Guillaume Cottenceau <gc@mnc.ch>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: "pgsql-performa." <pgsql-performance@postgresql.org>
Subject: Re: much slower query in production
References: <878skpnx0u.fsf@mnc.ch> <20200226162809.GZ31889@telsasoft.com>
	<87tv3dmdlu.fsf@mnc.ch>
	<CAMkU=1yOAikXEXV1dEJqnJ1d2D8aBBxy=3=+=hxt_qh-LLCZhg@mail.gmail.com>
Date: Thu, 27 Feb 2020 09:31:45 +0100
In-Reply-To: 
 <CAMkU=1yOAikXEXV1dEJqnJ1d2D8aBBxy=3=+=hxt_qh-LLCZhg@mail.gmail.com>
	(Jeff Janes's message of "Wed, 26 Feb 2020 18:17:58 -0500")
Message-ID: <87k148mnwu.fsf@mnc.ch>
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

Jeff Janes <jeff.janes 'at' gmail.com> writes:

> On Wed, Feb 26, 2020 at 1:02 PM Guillaume Cottenceau <gc@mnc.ch> wrote:
>
>  It is actually consistent with using a restored backup on the dev
>  computer, as my understanding is this comes out without any
>  garbage and like a perfectly vacuumed database.
>
> I think I got that backwards in my previous email. It is the
> dev that was restored, not the prod? But unless you went out of

Yes (prod was also restored not so long ago, when updating to pg
11.5 tho).

> your way to vacuum dev, it would not be perfectly vacuumed. If
> it were a logical restore, it would be perfectly unvacuumed,
> and if a physical restore would be in the same state of
> vacuuming as the database it was cloned from.
>
>  Btw do you have
>  any hint as to how to perform timings using production data which
>  are consistent with production? Backup/restore is maybe not the
>  way to go, but rather a block device level copy?
>
> block device copy seems like overkill, just using pg_basebackup should be good enough.
>
>  Since postgresql 8, I have to say I rely entirely on autovacuum,
>  and did not notice it could really run too infrequently for the
>  work and create such difference. I see in documentation a default
>  autovacuum_vacuum_scale_factor = 0.2, is that something that is
>  typically lowered globally, e.g. maybe on a fairly active system?
>  I am worried that changing that configuration for that table to
>  0.005 would fix this query and similar ones, but later I might
>  face the same situation on other tables. Or how would you elect
>  tables for a lowered value configuration?
>
> The autovacuum system has never been redesigned with the needs of index-only-scans in mind. If I have a table for which
> index-only scans are important, I'd set autovacuum_vacuum_scale_factor = 0 and set autovacuum_vacuum_threshold to about 5% of
> the number of blocks in the table. There is no syntax to say '5% of the number of blocks in the table' so you have to compute
> it yourself and hardcode the result, which makes it unsuitable for a global setting. And this still only addresses UPDATE and

It seems also difficult for us as this table grows over time (and
is trimmed only infrequently).

> DELETE operations, not INSERTs. If you have INSERT only or mostly table for which index-only-scans are important, you might
> need to set up cron jobs to do vacuuming.

Thanks!

-- 
Guillaume Cottenceau