Re: 10.1: hash index size exploding on vacuum full analyze

On Fri, Nov 17, 2017 at 11:50:57AM +0530, Ashutosh Sharma wrote:
> AP, Is there anything else happening in parallel with VACUUM that
> could lead to increase in the index table size.

Nope. System was quiet. It was, in fact, the only thing happening.

> Anyways, before i put my thoughts, i would like to summarize on what
> you have done here,
> 
> 1) Created hash index table on your base table with ff=90.

Yup.

> 2) You then realised that your base table is static and therefore
> thought of changing the index table fillfactor to 100. For that you
> altered the index table to set FF=100

Almost. :)

> 3) REINDEX your hash index table.

Nope. REINDEX does not do CONCURRENTLY so I created a minty fresh index.

Index was created like so:

create index concurrently on schema.table using hash (datum_id) with ( fillfactor = 100 );

> 4) Checked for the index table size. It got reduced from 309GB to 280GB.

Yup.

> 5) Ran VACUUM FULL ANALYZE and checked for the index table size. Now
> you saw the index table size as 709GB which was not expected. I think,

Yes.

> in hash index the table size should remain the same i.e 280GB in your
> case.

This was my thought also.

> I think, as Amit suggested, the first thing you can do is, share the
> index table statistics before and after VACUUM. Also, as i mentioned
> above, it would be worth checking if there is something that could be
> running in parallel with VACUUM.

Hopefully I have that now.

AP