Re: BUG #14635: Query is executed slower on hot standby slave database then on master database

On Thu, May 4, 2017 at 12:05 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vitaliy Gomenyuk <vgomenyuk(at)callfire(dot)com> writes:
> > [ same query slower on slave ]
>
> Hmm, the discrepancy is evidently in the larger bitmap index scan:
>
> > There is an execution plan from master:
> > -> Bitmap Index Scan on "OutgoingMessages_Status_StampToSend_Deleted"
> (cost=0.00..3556.90 rows=80249 width=0) (actual time=139.761..139.761
> rows=9158 loops=1)
> > Index Cond: ((om."Status" = 0) AND
> (om."Deleted" = false))
> > Buffers: shared hit=70252
>
> > There is an execution plan from slave:
> > -> Bitmap Index Scan on "OutgoingMessages_Status_StampToSend_Deleted"
> (cost=0.00..3556.90 rows=80249 width=0) (actual time=1470.853..1470.853
> rows=8671249 loops=1)
> > Index Cond: ((om."Status" = 0) AND
> (om."Deleted" = false))
> > Buffers: shared hit=70252
>
> The contents of the indexes should be the same, so why is the slave
> returning so many more rows? It has to be because the index entries are
> not marked as killed (known-dead-to-everybody), or not being treated as
> killed, in the slave. I vaguely recall that there's a difference in the
> rules for index entry visibility on slaves, but it's not clear to me why
> that should be.
>

The index cleanup by the full vacuum and vacuum one page are WAL logged,
so when they gets replayed on the slave, both the indexes must be same.

May be the WAL didn't replayed on the slave because of conflict transaction?
Or Any other scenarios it may be different?

Hi Vitaliy,

Is it possible for you check the status of the replication? and also is it
possible
for you to create another fresh slave and check whether the issue is
happening
there also?

Regards,
Hari Babu
Fujitsu Australia