Re: much slower query in production

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 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
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.

Cheers,

Jeff