Re: 10.1: hash index size exploding on vacuum full analyze - Mailing list pgsql-bugs

On Thu, Nov 16, 2017 at 09:48:13AM +0530, Amit Kapila wrote:
> 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.

FYI: Nuking the above and doing a create index run gave me a 280GB index again.

> 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.

Before VACUUM FULL:
   Schema     |                        Name                        | Type  |   Owner   |                 Table
      |  Size  | Description
---------------+----------------------------------------------------+-------+-----------+---------------------------------------+--------+-------------bundle_link
 | be5be0ac8_3ba5_407d_8183_2d05b9387e81_datum_id_idx | index | mdkingpin | be5be0ac8_3ba5_407d_8183_2d05b9387e81 | 273
GB| 

mdstash=# select * from
hash_metapage_info(get_raw_page('bundle_link.be5be0ac8_3ba5_407d_8183_2d05b9387e81_datum_id_idx',0))\gx 
-[ RECORD 1 ]---...---
magic     | 105121344
version   | 4
ntuples   | 9123458028
ffactor   | 409
bsize     | 8152
bmsize    | 4096
bmshift   | 15
maxbucket | 25165823
highmask  | 33554431
lowmask   | 16777215
ovflpoint | 71
firstfree | 10623106
nmaps     | 325
procid    | 456
spares    |
{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,10623106,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
mapp      |
{25165825,25198593,25231361,25264129,25296897,25329665,25362433,25395201,25427969,25460737,25493505,25526273,25559041,25591809,25624577,25657345,25690113,25722881,25755649,25788417,25821185,25853953,25886721,25919489,25952257,25985025,26017793,26050561,26083329,26116097,26148865,26181633,26214401,26247169,26279937,26312705,26345473,26378241,26411009,26443777,26476545,26509313,26542081,26574849,26607617,26640385,26673153,26705921,26738689,26771457,26804225,26836993,26869761,26902529,26935297,26968065,27000833,27033601,27066369,27099137,27131905,27164673,27197441,27230209,27262977,27295745,27328513,27361281,27394049,27426817,27459585,27492353,27525121,27557889,27590657,27623425,27656193,27688961,27721729,27754497,27787265,27820033,27852801,27885569,27918337,27951105,27983873,28016641,28049409,28082177,28114945,28147713,28180481,28213249,28246017,28278785,28311553,28344321,28377089,28409857,28442625,28475393,28508161,28540929,28573697,28606465,28639233,28672001,28704769,28737537,28770305,28803073,28835841,28868609,28901377,28934145,28966913,28999681,29032449,29065217,29097985,29130753,29163521,29196289,29229057,29261825,29294593,29327361,29360129,29392897,29425665,29458433,29491201,29523969,29556737,29589505,29622273,29655041,29687809,29720577,29753345,29786113,29818881,29851649,29884417,29917185,29949953,29982721,30015489,30048257,30081025,30113793,30146561,30179329,30212097,30244865,30277633,30310401,30343169,30375937,30408705,30441473,30474241,30507009,30539777,30572545,30605313,30638081,30670849,30703617,30736385,30769153,30801921,30834689,30867457,30900225,30932993,30965761,30998529,31031297,31064065,31096833,31129601,31162369,31195137,31227905,31260673,31293441,31326209,31358977,31391745,31424513,31457281,31490049,31522817,31555585,31588353,31621121,31653889,31686657,31719425,31752193,31784961,31817729,31850497,31883265,31916033,31948801,31981569,32014337,32047105,32079873,32112641,32145409,32178177,32210945,32243713,32276481,32309249,32342017,32374785,32407553,32440321,32473089,32505857,32538625,32571393,32604161,32636929,32669697,32702465,32735233,32768001,32800769,32833537,32866305,32899073,32931841,32964609,32997377,33030145,33062913,33095681,33128449,33161217,33193985,33226753,33259521,33292289,33325057,33357825,33390593,33423361,33456129,33488897,33521665,33554433,33587201,33619969,33652737,33685505,33718273,33751041,33783809,33816577,33849345,33882113,33914881,33947649,33980417,34013185,34045953,34078721,34111489,34144257,34177025,34209793,34242561,34275329,34308097,34340865,34373633,34406401,34439169,34471937,34504705,34537473,34570241,34603009,34635777,34668545,34701313,34734081,34766849,34799617,34832385,34865153,34897921,34930689,34963457,34996225,35028993,35061761,35094529,35127297,35160065,35192833,35225601,35258369,35291137,35323905,35356673,35389441,35422209,35454977,35487745,35520513,35553281,35586049,35618817,35651585,35684353,35717121,35749889,35782657,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

Time: 0.613 ms

mdstash=# select * from pgstathashindex('bundle_link.be5be0ac8_3ba5_407d_8183_2d05b9387e81_datum_id_idx');version |
bucket_pages| overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent 
---------+--------------+----------------+--------------+--------------+------------+------------+------------------
 4 |     25165824 |       10622781 |          325 |            0 | 9123458028 |          0 | 37.4567373970968 
(1 row)

Time: 2002419.406 ms (33:22.419)

After VACUUM FULL:
   Schema     |                        Name                        | Type  |   Owner   |                 Table
      |  Size  | Description
---------------+----------------------------------------------------+-------+-----------+---------------------------------------+--------+-------------bundle_link
 | be5be0ac8_3ba5_407d_8183_2d05b9387e81_datum_id_idx | index | mdkingpin | be5be0ac8_3ba5_407d_8183_2d05b9387e81 | 701
GB| 

-[ RECORD 1 ]---...---
magic     | 105121344
version   | 4
ntuples   | 9123458028
ffactor   | 409
bsize     | 8152
bmsize    | 4096
bmshift   | 15
maxbucket | 83886079
highmask  | 134217727
lowmask   | 67108863
ovflpoint | 78
firstfree | 7996259
nmaps     | 245
procid    | 456
spares    |
{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,7996259,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
mapp      |
{83886081,83918849,83951617,83984385,84017153,84049921,84082689,84115457,84148225,84180993,84213761,84246529,84279297,84312065,84344833,84377601,84410369,84443137,84475905,84508673,84541441,84574209,84606977,84639745,84672513,84705281,84738049,84770817,84803585,84836353,84869121,84901889,84934657,84967425,85000193,85032961,85065729,85098497,85131265,85164033,85196801,85229569,85262337,85295105,85327873,85360641,85393409,85426177,85458945,85491713,85524481,85557249,85590017,85622785,85655553,85688321,85721089,85753857,85786625,85819393,85852161,85884929,85917697,85950465,85983233,86016001,86048769,86081537,86114305,86147073,86179841,86212609,86245377,86278145,86310913,86343681,86376449,86409217,86441985,86474753,86507521,86540289,86573057,86605825,86638593,86671361,86704129,86736897,86769665,86802433,86835201,86867969,86900737,86933505,86966273,86999041,87031809,87064577,87097345,87130113,87162881,87195649,87228417,87261185,87293953,87326721,87359489,87392257,87425025,87457793,87490561,87523329,87556097,87588865,87621633,87654401,87687169,87719937,87752705,87785473,87818241,87851009,87883777,87916545,87949313,87982081,88014849,88047617,88080385,88113153,88145921,88178689,88211457,88244225,88276993,88309761,88342529,88375297,88408065,88440833,88473601,88506369,88539137,88571905,88604673,88637441,88670209,88702977,88735745,88768513,88801281,88834049,88866817,88899585,88932353,88965121,88997889,89030657,89063425,89096193,89128961,89161729,89194497,89227265,89260033,89292801,89325569,89358337,89391105,89423873,89456641,89489409,89522177,89554945,89587713,89620481,89653249,89686017,89718785,89751553,89784321,89817089,89849857,89882625,89915393,89948161,89980929,90013697,90046465,90079233,90112001,90144769,90177537,90210305,90243073,90275841,90308609,90341377,90374145,90406913,90439681,90472449,90505217,90537985,90570753,90603521,90636289,90669057,90701825,90734593,90767361,90800129,90832897,90865665,90898433,90931201,90963969,90996737,91029505,91062273,91095041,91127809,91160577,91193345,91226113,91258881,91291649,91324417,91357185,91389953,91422721,91455489,91488257,91521025,91553793,91586561,91619329,91652097,91684865,91717633,91750401,91783169,91815937,91848705,91881473,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

Time: 69.237 ms

mdstash=# select * from pgstathashindex('bundle_link.be5be0ac8_3ba5_407d_8183_2d05b9387e81_datum_id_idx');version |
bucket_pages| overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent    
---------+--------------+----------------+--------------+--------------+------------+------------+------------------
 4 |     83886080 |        7996014 |          245 |            0 | 9123458028 |          0 | 75.6390388675015 
(1 row)

Time: 2474290.172 ms (41:14.290)

Tell me if you need me to keep the index around.

AP