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

On Thu, Nov 16, 2017 at 4:59 AM, AP <pgsql@inml.weebeastie.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
additionalsplits.
 

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com