Re: much slower query in production

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

> On Wed, Feb 26, 2020 at 1:02 PM Guillaume Cottenceau <gc(at)mnc(dot)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