Re: Slow planning time for simple query

13.06.2018 12:40, Maksim Milyutin wrote:

> On 09.06.2018 22:49, Tom Lane wrote:
>
>> Maksim Milyutin<milyutinma(at)gmail(dot)com> writes:
>>> On hot standby I faced with the similar problem.
>>> ...
>>> is planned 4.940 ms on master and *254.741* ms on standby.
>> (I wonder though why, if you executed the same query on the master,
>> its setting of the index-entry-is-dead bits didn't propagate to the
>> standby.)
>
> I have verified the number dead item pointers (through pageinspect
> extension) in the first leaf page of index participating in query
> ('main.message_instance_pkey') on master and slave nodes and have
> noticed a big difference.
>
> SELECT * FROM monitoring.bt_page_stats('main.message_instance_pkey',
> 3705);
>
> On master:
>
>  blkno | type | live_items | dead_items | avg_item_size | page_size |
> free_size | btpo_prev | btpo_next | btpo | btpo_flags
> -------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
>   3705 | l    |          1 |         58 |            24 |      8192
> |      6496 |         0 |      3719 |    0 | 65
>
> On standby:
>
>  blkno | type | live_items | dead_items | avg_item_size | page_size |
> free_size | btpo_prev | btpo_next | btpo | btpo_flags
> -------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
>   3705 | l    |         59 |          0 |            24 |      8192
> |      6496 |         0 |      3719 |    0 |          1
>
>

In this point I want to highlight the issue that the changes in
*lp_flags* bits (namely, set items as dead) for index item pointers
doesn't propagate from master to replica in my case. As a consequence,
on standby I have live index items most of which on master are marked as
dead. And my queries on planning stage are forced to descent to heap
pages under *get_actual_variable_range* execution that considerately
slows down planning.

Is it bug or restriction of implementation or misconfiguration of
WAL/replication?

--
Regards,
Maksim Milyutin