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
Date: Thu, 27 Feb 2020 09:31:45 +0100
Message-ID: <87k148mnwu.fsf@mnc.ch> (raw)
In-Reply-To: <CAMkU=1yOAikXEXV1dEJqnJ1d2D8aBBxy=3=+=hxt_qh-LLCZhg@mail.gmail.com>
References: <878skpnx0u.fsf@mnc.ch>
<20200226162809.GZ31889@telsasoft.com>
<87tv3dmdlu.fsf@mnc.ch>
<CAMkU=1yOAikXEXV1dEJqnJ1d2D8aBBxy=3=+=hxt_qh-LLCZhg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gc@mnc.ch, jeff.janes@gmail.com
Subject: Re: much slower query in production
In-Reply-To: <87k148mnwu.fsf@mnc.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox