On Wed, 2022-10-12 at 10:51 +0200, Guillaume Lelarge wrote:
> Just finished my phone call. So, they definitely have their performance back. All they did was a VACUUM on two
tables.
>
> If I understand correctly, during "normal" operations, some information is stored on the primary
> and sent to standbys. For some reason, only the primary take them into account, standbys ignore them.
> That would explain why, when we promoted a standby without doing anything else, it had much better
> performance. VACUUM fixes the issue on a standby, probably by storing this information in a different
> way. After VACUUM, standbys stop ignoring this information, which helps get the performance back.
>
> That sounds like a plausible explanation. I still have questions if you don't mind:
> * what is this information?
> * where is it stored? my guess would be indexes
> * why is it ignored on standbys and used on primary?
That sounds indeed like killed (LP_DEAD) index tuples on the primary.
Peter says they are ignored on the standby anyway, so on the standby
PostgreSQL went through a lot of index entries pointing to dead table
tuples, and it took a long time to find the maximal entry in the table,
which is done by the optimizer.
VACUUM removed those dead tuples and their associated index entries
on both primary and standby.
Yours,
Laurenz Albe