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