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

On Sat, May 13, 2017 at 3:30 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, May 4, 2017 at 10:26:17AM +1000, Haribabu Kommi wrote:
> > 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?
>
> We don't WAL-log hint bits. Could that affect queries running on the
> standbys?
>

Yes, this may be a reason for extra number of records that are present in
the
index.

Hi Vitaly,

Hint bits are used to mark tuples as created and/or deleted by transactions
that are known committed or aborted. The changes to the hint bits are not
WAL logged. These are WAL logged when the data checksums are enabled
or wal_log_hintbits GUC is set, but these two options are not available in
PostgreSQL version 9.2

Is it possible for you test the query when you execute the reindex of the
index?

or

Try to add pgstattuple and pageinspect extensions and verify whether the
index
is same in both master and standby.

If there exists more number of records in standby compared to master, this
may
be because of some cleanup that is happening in master and not done in the
standby.

Regards,
Hari Babu
Fujitsu Australia