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
