Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database - Mailing list pgsql-bugs

On Thu, May 18, 2017 at 9:26 AM, Vitaliy Gomenyuk <vgomenyuk@callfire.com> wrote:

2. I'm not sure that the problem is with not WAL-logged hint bits, because due to my next investigation:

  2.0. We have two equal servers for master and slave database (CPU, RAM, HDD).

  2.1. On master database I'm running a query, it executes in 138.116 ms

  2.2. On a hot-standby slave database with working replication process I'm running the same query, it executes in 1623.496 ms (much slower)

  2.3. Then on that hot-standby slave database I've created a trigger file, which stops a replication process immediately and right after that, I'm running the same query, it executes in 132.354 ms (fast like on master). 

  2.4. During the time when I'm creating a trigger file (less then 1 second), indexes in that table can't be changed. Even more, that slave hot-standby database was created from the full master copy just before I started test. So master and slave databases are equal. One difference is turned on replication process in a hot-standby mode. So only a replication process can cause this behaviour.

If the index hint bits were set on the master and then a base backup is taken, then those bits are set on the replica created from that backup.  But it does not use the index hint bits while it is in hot standby mode.  But when it is promoted, it will start using them.

 

 

But why and how to improve the situation?

Vacuum your table (on the master).  

Cheers,

Jeff