Horton

Re: [HACKERS] GUC for cleanup indexes threshold.

Masahiko Sawada <sawada.mshk@gmail.com>

View thread View on postgr.es
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, y.sokolov@postgrespro.ru, Peter Geoghegan <pg@bowt.ie>, Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>, David Steele <david@pgmasters.net>, Amit Kapila <amit.kapila16@gmail.com>, "Ideriha, Takeshi" <ideriha.takeshi@jp.fujitsu.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Kuntal Ghosh <kuntalghosh.2007@gmail.com>, pgsql-hackers-owner@postgresql.org
Date: 2017-11-29T15:06:25Z
Lists: pgsql-hackers 
On Wed, Nov 29, 2017 at 11:05 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 25 September 2017 at 22:34, Kyotaro HORIGUCHI
> <horiguchi.kyotaro@lab.ntt.co.jp> wrote:
>
>>> > Here is a small patch that skips scanning btree index if no pending
>>> > deleted pages exists.
>>> > It detects this situation by comparing pages_deleted with pages_free.
>>
>> It seems to work to prevent needless cleanup scans.
>
> So this leaves us in the situation that
>
> 1. Masahiko's patch has unresolved problems
> 2. Yura's patch works and is useful
>
> Unless there is disagreement on the above, it seems we should apply
> Yura's patch (an edited version, perhaps).
>

IIRC the patches that makes the cleanup scan skip has a problem
pointed by Peter[1], that is that we stash an XID when a btree page is
deleted, which is used to determine when it's finally safe to recycle
the page. Yura's patch doesn't have that problem?

[1] https://www.postgresql.org/message-id/CAH2-Wz%3D1%3Dt5fcGGfarQGcAWBqaCh%2BdLMjpYCYHpEyzK8Qg6OrQ%40mail.gmail.com

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Commits

  1. Fix upper limit for vacuum_cleanup_index_scale_factor

  2. Increase upper limit for vacuum_cleanup_index_scale_factor

  3. Fixes for vacuum_cleanup_index_scale_factor GUC option

  4. Skip full index scan during cleanup of B-tree indexes when possible