Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

> From: Peter Geoghegan <pg(at)bowt(dot)ie>
> Sent: June 26, 2019 12:09 PM
> Subject: Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
>
> > >
> > > I suspect that you might find that the enhancements to B-Tree indexes
> > > that went into Postgres 12 would help with this workload, especially
> > > if you notice that this happens with indexes that have a lot of duplicates
> > >
>
> >
> > I had not noticed that, thanks for pointing it out. Yes , in my workload most of the indexes in question
> > are non-unique and some have very low key card. I will try out the pg-12 beta when I get a chance.
> >
>
>
> It's easy to show problems with very low cardinality indexes in the
> old code. You'll definitely notice a difference there.
>

I've run a comparison of pg-12beta2 with an older release, pg-9.4.6, running the same intensive-delete-insert workload as mentioned before in this thread, and would appreciate if you could comment on the results, especially as to whether they are roughly in line with your expectation.
I also have one question about a new-in-pg-11 config parm.

Briefly, the workload involves a repetition of a loop in which , on one single table which has 8 indexes, 2 unique and 6 non-unique,
about 4300 records are deleted, and for each of those records, a corresponding record is inserted in which one or more of the non-unique key values are modified to values which are not present in the relevant index at that point. In other words, across all the indexes , 4300 key-tids are deleted and then 4300 new key-tids are inserted. At the end of each loop there is zero net change in counts of records and keys but possibly some increase in numbers of pages, which is what the test is interested in as well as overall throughput rate.

For this test, I did not modify the index default fill factors which therefore remained at 90%, in order to make a stab at evaluating not setting explicit fillfactor. In each case the indexes were either freshly loaded or else reindexed to have the same starting density. Here are counts and sizes after 768 iterations

| tbpages | tbtuples | ixname | isuniq | livetuples | deadtuples | avg_leaf_density | ixpages
+---------+--------------+------------------------------+--------+------------+------------+------------------+---------

pg-9.4.6
---------------------

| 32160 | 2.55548e+06 | metadata_value_boolean | f | 2932852 | 0 | 46.39 | 13535
| 32160 | 2.55548e+06 | metadata_value_field_id | f | 2932852 | 0 | 48.58 | 12916
| 32160 | 2.55548e+06 | metadata_value_floatnumber | f | 2932852 | 0 | 45.97 | 13658
| 32160 | 2.55548e+06 | metadata_value_longnumber | f | 2932852 | 0 | 48.26 | 13009
| 32160 | 2.55548e+06 | metadata_value_owner_field_u | t | 2932852 | 0 | 58.69 | 14990
| 32160 | 2.55548e+06 | metadata_value_owner_id | f | 2932852 | 0 | 53.06 | 11817
| 32160 | 2.55548e+06 | metadata_value_pkey | t | 2932852 | 0 | 57.83 | 10842
| 32160 | 2.55548e+06 | metadata_value_timestamp | f | 2932852 | 0 | 45.96 | 13663

pg-12beta2
---------------------

| 41814 | 2.519268e+06 | metadata_value_boolean | f | 2519268 | 6766 | 63.17 | 10768
| 41814 | 2.519268e+06 | metadata_value_field_id | f | 2519268 | 6766 | 68.7 | 12031
| 41814 | 2.519268e+06 | metadata_value_floatnumber | f | 2519268 | 6766 | 61.48 | 11225
| 41814 | 2.519268e+06 | metadata_value_longnumber | f | 2519268 | 6766 | 58.34 | 12397
| 41814 | 2.519268e+06 | metadata_value_owner_field_u | t | 2519268 | 6766 | 61.69 | 14780
| 41814 | 2.519268e+06 | metadata_value_owner_id | f | 2519268 | 6766 | 48.86 | 12947
| 41814 | 2.519268e+06 | metadata_value_pkey | t | 2519268 | 6766 | 59.71 | 11076
| 41814 | 2.519268e+06 | metadata_value_timestamp | f | 2519268 | 6766 | 57.81 | 12295

Overall, pg-12beta2 yielded a 6.7% reduction in sizes (total pages) of indexes, which was most noticable with the 6 non-unique ones.
In fact the primary-key index was larger with pg-12. Would you have expected better than 6.7%? Although a welcome improvement, I think it is not enough to justify stopping use of setting a lower explicit FILLFACTOR. Which then brings me back to thinking there is a case for the subject of this thread, an automatic way to preserve density.

Secondary points:

I did not expect to see the number of table pages grow so much larger for pg-12 than for pg-9.4. The number of table pages was almost identical at the start of each run. However this was not the focus of the test.

Also, although not shown in those tables, pg-12 was around 4.5 times faster in completing those 768 iterations, an enormous improvement.

And one question :
I notice that in some pg-11 release, a new config parameter appeared :
vacuum_cleanup_index_scale_factor
specifies the fraction of the total number of heap tuples counted in the previous statistics collection that can be inserted without incurring an index scan at the VACUUM cleanup stage.

I have not researched this at all and nor did I set it to anything for my pg-12beta2 run, but it sounds as though maybe it could be relevant to this kind of workload - Is that so?

Cheers John Lumby