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

Hello Haribabu,

Thank you for your answer.

There is status of the replication:
select pg_last_xlog_receive_location() "receive_location",
pg_last_xlog_replay_location() "replay_location", pg_is_in_recovery()
"recovery_status", pg_is_xlog_replay_paused() "replication_paused", now() -
pg_last_xact_replay_timestamp() "replication_delay";

receive_location | replay_location | recovery_status | replication_paused |
replication_delay
------------------+-----------------+-----------------+--------------------+-------------------
66A/8F77A330 | 66A/8F77A330 | t | f
| 00:00:00.015721
(1 row)

We have several slave servers and I also have setup new fresh slave
databases several times - I got the same situation each time.

I still need help with it.

Waiting for your response. Thanks.

On Thu, May 4, 2017 at 3:26 AM, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
wrote:

>
>
> 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
>

--

Best regards,
*Vitaliy Gomenyuk* Senior DBA
vgomenyuk(at)callfire(dot)com | +380 67 220 5903
email <vgomenyuk(at)callfire(dot)com> | website <https://www.callfire.com/> |
Facebook <https://www.facebook.com/callfire> | Twitter
<https://www.twitter.com/callfire>