Re: REINDEX : new parameter to preserve current average leaf densityas new implicit FILLFACTOR - Mailing list pgsql-general

From John Lumby
Subject Re: REINDEX : new parameter to preserve current average leaf densityas new implicit FILLFACTOR
Date
Msg-id DM6PR06MB55629FC150D4C46D0312B63AA3F60@DM6PR06MB5562.namprd06.prod.outlook.com
Whole thread Raw
In response to Re: REINDEX : new parameter to preserve current average leaf densityas new implicit FILLFACTOR  (Peter Geoghegan <pg@bowt.ie>)
Responses Re: REINDEX : new parameter to preserve current average leaf densityas new implicit FILLFACTOR
List pgsql-general
Thanks Peter

> From: Peter Geoghegan <pg@bowt.ie>
> Sent: July 8, 2019 1:39 PM
> Subject: Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
>
> Perhaps you didn't take deleted_pages into account -- there must be
> free space that is reusable by the index that has yet to be reused.
> It would probably make sense to subtract that across the board.
>

Correct,  I did not,   but will do so for the next runs.

>
> I don't think that a test case that runs VACUUM when there are only
> 4300 deletions and 4300 insertions is particularly realistic, in
> general. You might see a larger difference if there was more churn
> between each VACUUM run.
>

Actually the test workload does not run any explicit VACUUM command,
it relies on autovacuum with these settings
(same settings for 9.4 and 12beta2)

 autovacuum                      | on      |
 autovacuum_analyze_scale_factor | 0.4     |
 autovacuum_analyze_threshold    | 50000   |
 autovacuum_max_workers          | 6       |
 autovacuum_naptime              | 20      | s
 autovacuum_vacuum_cost_delay    | 0       | ms
 autovacuum_vacuum_cost_limit    | 9999    |
 autovacuum_vacuum_scale_factor  | 0       |
 autovacuum_vacuum_threshold     | 2000    |
 autovacuum_work_mem             | 1048576 | kB


To correspond to your " more churn between each VACUUM"
Would you then suggest increasing
autovacuum_vacuum_cost_delay and/or  autovacuum_vacuum_scale_factor?

Cheers,  John Lumby


pgsql-general by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: REINDEX : new parameter to preserve current average leaf densityas new implicit FILLFACTOR
Next
From: Tom Mercha
Date:
Subject: Re: Measuring the Query Optimizer Effect: Turning off the QO?