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

On Thu, Nov 16, 2017 at 10:36:39AM +0530, Amit Kapila wrote:
> On Thu, Nov 16, 2017 at 10:32 AM, AP <pgsql(at)inml(dot)weebeastie(dot)net> wrote:
> > On Thu, Nov 16, 2017 at 09:48:13AM +0530, Amit Kapila wrote:
> >> 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.
> >
> > For the latter is this correct?
> >
> > select * from hash_metapage_info(get_raw_page('exploding_index', 0))\gx
>
> I think it should work, but please refer documentation [1] for exact usage.
>
> [1] - https://www.postgresql.org/docs/devel/static/pageinspect.html#idm191242

Cool. That's where I got the usage from. The "0" argument of get_raw_page
seemed somewhat arbitrary so I wasn't sure if that was correct.

If all's well, though, then I'll have some values Tuesday/Wednesday. The
VACUUM FULL alone takes ~1.5 days at least and pgstathashindex() is not
the fastest duck in the west and I can't start VACCUMing until it's done
working out the "before" stats.

AP