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

On Thu, Nov 16, 2017 at 9:48 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Thu, Nov 16, 2017 at 4:59 AM, AP <pgsql(at)inml(dot)weebeastie(dot)net> wrote:
>> I've some tables that'll never grow so I decided to replace a big index
>> with one with a fillfactor of 100. That went well. The index shrunk to
>> 280GB. I then did a vacuum full analyze on the table to get rid of any
>> cruft (as the table will be static for a long time and then only deletes
>> will happen) and the index exploded to 701GB. When it was created with
>> fillfactor 90 (organically by filling the table) the index was 309GB.
>>
>
> Sounds quite strange. I think during vacuum it leads to more number
> of splits than when the original data was loaded. By any chance do
> you have a copy of both the indexes (before vacuum full and after
> vacuum full)? Can you once check and share the output of
> pgstattuple-->pgstathashindex() and pageinspect->hash_metapage_info()?
> I wanted to confirm if the bloat is due to additional splits.
>

This looks surprising to me too...

AP, Is there anything else happening in parallel with VACUUM that
could lead to increase in the index table size.

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.
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
3) REINDEX your hash index table.
4) Checked for the index table size. It got reduced from 309GB to 280GB.
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,
in hash index the table size should remain the same i.e 280GB in your
case.

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.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com