On Wed, Feb 26, 2020 at 11:17 AM Guillaume Cottenceau <gc@mnc.ch> wrote:
Dear all,
I am facing a much, much slower query in production than on my development computer using a restored production backup, and I don't understand why nor I see what I could do to speedup the query on production :/
You've already seen a VACUUM fixed it.
This must have been a logical restore (from pg_dump), not a physical restore (from pg_basebackup for example) correct?
A physical restore should have resulted in a database in the same state of vacuuming as its source was. A logical restore will not, and since it is an append-only process it will not trigger autovacuum to run, either.
If you do a logical restore, probably the first should you do afterwards is a VACUUM ANALYZE.