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

On Fri, May 26, 2017 at 10:12 AM, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>
wrote:

>
>
> On Fri, May 26, 2017 at 9:42 PM, Vitaliy Gomenyuk <vgomenyuk(at)callfire(dot)com>
> wrote:
>
>> Hello,
>>
>> So we have two reasons of current problem:
>>
>> 1. Changes to hint bits are not WAL logged => can be fixed by upgrading
>> PostgreSQL at least to 9.4 version and turning on parameter "wal_log_hints";
>>
>> 2. Slave DB does not use the index hint bits while it is in hot standby
>> mode => how this can be fixed?
>>
>
> The main reason for slave DB not use the index hint bits because of
> difference
> in between xmin value of both master and standby, This is required for
> proper
> MVCC behavior for the queries that are running on the standby.
>
> Following is the comment from the code that explains more.
>
> /*
> * During recovery we ignore killed tuples and don't bother to kill them
> * either. We do this because the xmin on the primary node could easily be
> * later than the xmin on the standby node, so that what the primary
> * thinks is killed is supposed to be visible on standby. So for correct
> * MVCC for queries during recovery we must ignore these hints and check
> * all tuples. Do *not* set ignore_killed_tuples to true when running in a
> * transaction that was started during recovery. xactStartedInRecovery
> * should not be altered by index AMs.
> */
>

But why can't the same mechanisms used on tables work on indexes, which is
to wait out or conflict out any too-old transactions on the standby? Is
there a fundamental reason for that, or just that no one got around to
implementing it?

Cheers,

Jeff