From: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> |
---|---|
To: | Maksim Milyutin <milyutinma(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)lists(dot)postgresql(dot)org, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Slow planning time for simple query |
Date: | 2018-06-17 05:15:35 |
Message-ID: | CAA4eK1JHqW9uvnoaguwK5BgHKHcqteRg1CyPTaRGS9FJOm8kJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Thu, Jun 14, 2018 at 4:34 AM, Maksim Milyutin <milyutinma(at)gmail(dot)com> wrote:
> 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?
>
It is not a misconfiguration issue.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Benjie Gillam | 2018-06-17 08:05:21 | Detecting functions installed by an extension |
Previous Message | Amit Kapila | 2018-06-17 05:13:38 | Re: Slow planning time for simple query |
From | Date | Subject | |
---|---|---|---|
Next Message | Konstantin Knizhnik | 2018-06-17 07:05:42 | Re: WAL prefetch |
Previous Message | Amit Kapila | 2018-06-17 05:13:38 | Re: Slow planning time for simple query |