score:1

This is a "hot" standby. A "warm" standby is constantly replaying WAL records, but does not allow anyone to connect in order to do (read-only) queries against it.

It looks like the query on the standby is setting a massive number of hint bits on table rows which are no longer visible. Or at least, I don't know what else could explain the

dirtied=565,583

on the bitmap heap scan. But once set, they shouldn't need to be set again, so what happens if you repeat the same query back to back? The first execution should clear a path for the second one to be much faster.

It also looks like this table is woefully under-vacuumed. Now it can't be undervacuumed just on the standby of course, because as you note vacuuming can only occur on the master. But being under-vacuumed can have a worse penalty on the standby than it does on the master.


More questions

More questions with similar tag