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

Hello, guys,

Any updates on this?

Thank you in advance.

On Mon, May 8, 2017 at 4:22 PM, Vitaliy Gomenyuk <vgomenyuk(at)callfire(dot)com>
wrote:

> 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 <+380%2067%20220%205903>
> email <vgomenyuk(at)callfire(dot)com> | website <https://www.callfire.com/> |
> Facebook <https://www.facebook.com/callfire> | Twitter
> <https://www.twitter.com/callfire>
>

--

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>