Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database

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

Vitaliy Gomenyuk <vgomenyuk@callfire.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