10.1: hash index size exploding on vacuum full analyze
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.
I would've expected the index size to, at worst, remain constant rather
than explode. Am I wrong or is this a bug?
PostgreSQL is v10.1. Original index created on v10.0.
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 additional splits.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
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.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.
I'll see what I can do. Currently vacuuming the table without the index
so that I can then do a create index concurrently and get back my 280GB
index (it's how I got it in the first place). Namely:
create index concurrently on ... using hash (datum) with ( fillfactor = 100 );
I've got more similar tables, though.
AP
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
AP
On Thu, Nov 16, 2017 at 10:32 AM, AP <pgsql@inml.weebeastie.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]https://www.postgresql.org/docs/devel/static/pageinspect.html#idm191242 for exact usage.
[1]: https://www.postgresql.org/docs/devel/static/pageinspect.html#idm191242
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
On Thu, Nov 16, 2017 at 10:36:39AM +0530, Amit Kapila wrote:
On Thu, Nov 16, 2017 at 10:32 AM, AP <pgsql@inml.weebeastie.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
On Thu, Nov 16, 2017 at 10:00 AM, AP <pgsql@inml.weebeastie.net> wrote:
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.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.I'll see what I can do. Currently vacuuming the table without the index
so that I can then do a create index concurrently and get back my 280GB
index (it's how I got it in the first place). Namely:
One possible theory could be that the calculation for initial buckets
required for the index has overestimated the number of buckets. I
think this is possible because we choose the initial number of buckets
based on the number of tuples, but actually while inserting the values
we might have created more of overflow buckets rather than using the
newly created primary buckets. The chances of such a misestimation
are more when there are duplicate values. Now, if that is true, then
actually one should see the same size of the index (as you have seen
after vacuum full ..) when you create an index on the table with the
same values in index columns.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
On Thu, Nov 16, 2017 at 9:48 AM, Amit Kapila <amit.kapila16@gmail.com> 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.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
On Fri, Nov 17, 2017 at 7:58 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Thu, Nov 16, 2017 at 10:00 AM, AP <pgsql@inml.weebeastie.net> wrote:
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.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.I'll see what I can do. Currently vacuuming the table without the index
so that I can then do a create index concurrently and get back my 280GB
index (it's how I got it in the first place). Namely:One possible theory could be that the calculation for initial buckets
required for the index has overestimated the number of buckets. I
think this is possible because we choose the initial number of buckets
based on the number of tuples, but actually while inserting the values
we might have created more of overflow buckets rather than using the
newly created primary buckets. The chances of such a misestimation
are more when there are duplicate values. Now, if that is true, then
actually one should see the same size of the index (as you have seen
after vacuum full ..) when you create an index on the table with the
same values in index columns.
Amit, I think what you are trying to put here is that the estimation
on number of hash buckets required is calculated based on the number
of tuples in the base table but during this calculation we are not
aware of the fact that the table contains more of the duplicate values
or not. If it contains more of a duplicate values then during index
insertion it would start adding overflow page and many of the hash
index buckets added at start i.e. during hash index size estimation
would remain unused. If this is true then i think hash index would not
be the right choice. However, this is might not be exactly related to
what AP has reported here.
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
On Fri, Nov 17, 2017 at 11:58 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
On Fri, Nov 17, 2017 at 7:58 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Thu, Nov 16, 2017 at 10:00 AM, AP <pgsql@inml.weebeastie.net> wrote:
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.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.I'll see what I can do. Currently vacuuming the table without the index
so that I can then do a create index concurrently and get back my 280GB
index (it's how I got it in the first place). Namely:One possible theory could be that the calculation for initial buckets
required for the index has overestimated the number of buckets. I
think this is possible because we choose the initial number of buckets
based on the number of tuples, but actually while inserting the values
we might have created more of overflow buckets rather than using the
newly created primary buckets. The chances of such a misestimation
are more when there are duplicate values. Now, if that is true, then
actually one should see the same size of the index (as you have seen
after vacuum full ..) when you create an index on the table with the
same values in index columns.Amit, I think what you are trying to put here is that the estimation
on number of hash buckets required is calculated based on the number
of tuples in the base table but during this calculation we are not
aware of the fact that the table contains more of the duplicate values
or not. If it contains more of a duplicate values then during index
insertion it would start adding overflow page and many of the hash
index buckets added at start i.e. during hash index size estimation
would remain unused. If this is true then i think hash index would not
be the right choice.
Hmm, I am not sure that is the conclusion we can draw from this
behavior as we can change it if required. However, before drawing any
conclusions based on this theory, we should first try to find what is
the actual problem.
However, this is might not be exactly related to
what AP has reported here.
Yeah, quite possible.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
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
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
On Mon, Nov 20, 2017 at 5:01 AM, AP <pgsql@inml.weebeastie.net> wrote:
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
...
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 | 456Time: 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)
Based on above data, we can easily see that after vacuum full, there
is a huge increase in free_percent which is mostly due to the
additional bucket_pages after vacuum full. See the below calculation:
Index size difference = 701 - 273 = 428GB
Bucket page size difference = 83886080 - 25165824 = 58720256 = 448GB
Overflow page size difference = 7996014 - 10622781 = -2626767 = -20GB
So, if we just add the difference of bucket pages and overflow pages
size, it will give us the difference of size you are seeing after
vacuum full. So, this clearly indicates the theory I was speculating
above that somehow the estimated number of tuples (based on which
number of buckets are computed) is different when we do a vacuum full.
On further looking into it, I found that the relcache entry for a
relation doesn't have the correct value for relpages and reltuples
during vacuum full due to which estimate_rel_size can give some size
which might be quite different and then hashbuild can create buckets
which it might not even need to populate the tuples. I am not sure if
it is expected to have uninitialized (0) values for these attributes
during vacuum full, but I see that in swap_relation_files when we swap
the statistics, we are assuming that new rel has freshly-updated stats
which I think is not true. This needs some further investigation.
Another angle to look at it is that even if the values of relpages and
reltuples is not updated why we get such a wrong estimation by
estimate_rel_size. I think to some extent it depends on the schema of
the table, so is it possible for you to share schema of the table.
Tell me if you need me to keep the index around.
I don't think so, but till we solve the problem there is no harm in
keeping it if possible because one might want some information at a
later stage to debug this problem. OTOH, if you have space crunch
then feel free to delete it.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
On Mon, Nov 20, 2017 at 01:26:50PM +0530, Amit Kapila wrote:
Another angle to look at it is that even if the values of relpages and
reltuples is not updated why we get such a wrong estimation by
estimate_rel_size. I think to some extent it depends on the schema of
the table, so is it possible for you to share schema of the table.
Hi,
Schema's simple:
CREATE TABLE link (
datum_id BYTEA NOT NULL,
ids BYTEA NOT NULL
);
ALTER TABLE link ALTER COLUMN datum_id SET STATISTICS 10000;
ALTER TABLE link ALTER COLUMN ids SET STATISTICS 0;
ALTER TABLE link SET ( AUTOVACUUM_ANALYZE_SCALE_FACTOR = 0.001, AUTOVACUUM_VACUUM_SCALE_FACTOR = 0.001 );
CREATE INDEX ON link USING hash (datum_id) WITH ( FILLFACTOR = 90 );
That's for the live table. Then I move it aside and recreate the index
with FILLFACTOR = 100.
Tell me if you need me to keep the index around.
I don't think so, but till we solve the problem there is no harm in
keeping it if possible because one might want some information at a
later stage to debug this problem. OTOH, if you have space crunch
then feel free to delete it.
No worries. I'll keep it around for as long as I can.
AP
On Mon, Nov 20, 2017 at 1:26 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Nov 20, 2017 at 5:01 AM, AP <pgsql@inml.weebeastie.net> wrote:
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.Based on above data, we can easily see that after vacuum full, there
is a huge increase in free_percent which is mostly due to the
additional bucket_pages after vacuum full. See the below calculation:Index size difference = 701 - 273 = 428GB
Bucket page size difference = 83886080 - 25165824 = 58720256 = 448GB
Overflow page size difference = 7996014 - 10622781 = -2626767 = -20GB
So, if we just add the difference of bucket pages and overflow pages
size, it will give us the difference of size you are seeing after
vacuum full. So, this clearly indicates the theory I was speculating
above that somehow the estimated number of tuples (based on which
number of buckets are computed) is different when we do a vacuum full.
On further looking into it, I found that the relcache entry for a
relation doesn't have the correct value for relpages and reltuples
during vacuum full due to which estimate_rel_size can give some size
which might be quite different and then hashbuild can create buckets
which it might not even need to populate the tuples. I am not sure if
it is expected to have uninitialized (0) values for these attributes
during vacuum full, but I see that in swap_relation_files when we swap
the statistics, we are assuming that new rel has freshly-updated stats
which I think is not true. This needs some further investigation.
I think if we update the stats in copy_heap_data after copying the
data, then we don't see such problem. Attached patch should fix the
issue. You can try this patch to see if it fixes the issue for you.
You might want to wait for a day or so to see if anyone else has any
opinion on the patch or my analysis.
Another angle to look at it is that even if the values of relpages and
reltuples is not updated why we get such a wrong estimation by
estimate_rel_size. I think to some extent it depends on the schema of
the table, so is it possible for you to share schema of the table.
Schema's simple:
CREATE TABLE link (
datum_id BYTEA NOT NULL,
ids BYTEA NOT NULL
);
ALTER TABLE link ALTER COLUMN datum_id SET STATISTICS 10000;
ALTER TABLE link ALTER COLUMN ids SET STATISTICS 0;
I think the reason for getting totally off stats during
estimate_rel_size is that for the second column you have set
statistics to 0. I think if you keep it to default or some reasonable
number, then you won't get such a behavior. Anyhow, I think
irrespective of the value of stats, the relcache entry should also be
updated as explained above.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Attachments:
update_stats_vacuum_full_v1.patchapplication/octet-stream; name=update_stats_vacuum_full_v1.patchDownload+30-0
On Tue, Nov 21, 2017 at 05:22:18PM +0530, Amit Kapila wrote:
I think if we update the stats in copy_heap_data after copying the
data, then we don't see such problem. Attached patch should fix the
issue. You can try this patch to see if it fixes the issue for you.
You might want to wait for a day or so to see if anyone else has any
opinion on the patch or my analysis.
I'd love to but I wont be able to now for a week or two. The DB in question
is moving towards liveness but, once it's live I can work on a copy to see
if things become good. If I can get that happening sooner I'll grab that
chance.
Schema's simple:
CREATE TABLE link (
datum_id BYTEA NOT NULL,
ids BYTEA NOT NULL
);
ALTER TABLE link ALTER COLUMN datum_id SET STATISTICS 10000;
ALTER TABLE link ALTER COLUMN ids SET STATISTICS 0;I think the reason for getting totally off stats during
estimate_rel_size is that for the second column you have set
statistics to 0. I think if you keep it to default or some reasonable
number, then you won't get such a behavior. Anyhow, I think
Hmm. I wanted Postgres to ignore that column as it'll never be searched
on or sorted by or anything else. It's just there to provide a result.
Unless I missed the boat on this I'd like to keep that.
irrespective of the value of stats, the relcache entry should also be
updated as explained above.
Should the STATISTICS setting change index layout so drastically at
any rate?
AP
On Thu, Nov 23, 2017 at 11:01 AM, AP <pgsql@inml.weebeastie.net> wrote:
On Tue, Nov 21, 2017 at 05:22:18PM +0530, Amit Kapila wrote:
I think if we update the stats in copy_heap_data after copying the
data, then we don't see such problem. Attached patch should fix the
issue. You can try this patch to see if it fixes the issue for you.
You might want to wait for a day or so to see if anyone else has any
opinion on the patch or my analysis.I'd love to but I wont be able to now for a week or two. The DB in question
is moving towards liveness but, once it's live I can work on a copy to see
if things become good. If I can get that happening sooner I'll grab that
chance.
Okay.
Schema's simple:
CREATE TABLE link (
datum_id BYTEA NOT NULL,
ids BYTEA NOT NULL
);
ALTER TABLE link ALTER COLUMN datum_id SET STATISTICS 10000;
ALTER TABLE link ALTER COLUMN ids SET STATISTICS 0;I think the reason for getting totally off stats during
estimate_rel_size is that for the second column you have set
statistics to 0. I think if you keep it to default or some reasonable
number, then you won't get such a behavior. Anyhow, I thinkHmm. I wanted Postgres to ignore that column as it'll never be searched
on or sorted by or anything else. It's just there to provide a result.Unless I missed the boat on this I'd like to keep that.
irrespective of the value of stats, the relcache entry should also be
updated as explained above.Should the STATISTICS setting change index layout so drastically at
any rate?
Ideally not, that's why I proposed a patch to fix the actual cause of
the problem.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Hi!
I think if we update the stats in copy_heap_data after copying the
data, then we don't see such problem. Attached patch should fix the
issue. You can try this patch to see if it fixes the issue for you.
I'm afraid I'm not able to reproduce the problem which patch should fix.
What I did (today's master, without patch):
autovacuum off
pgbench -i -s 100
select relname, relpages, reltuples from pg_class where relname =
'pgbench_accounts';
relname | relpages | reltuples
------------------+----------+-----------
pgbench_accounts | 163935 | 1e+07
vacuum full pgbench_accounts;
# select relname, relpages, reltuples from pg_class where relname =
'pgbench_accounts';
relname | relpages | reltuples
------------------+----------+-----------
pgbench_accounts | 163935 | 1e+07
I've tried to add hash index to that table and print notice about number of
pages and tuples immediately after estimate_rel_size() in hashbuild(). hash
index got right estimation even I deleted all rows before vacuum full. What am I
doing wrong?
Patch looks good except, seems, updating stats is better to move to
swap_relation_files(), then it will work even for toast tables.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Hi,
On Fri, Dec 15, 2017 at 8:08 PM, Teodor Sigaev <teodor@sigaev.ru> wrote:
Hi!
I think if we update the stats in copy_heap_data after copying the
data, then we don't see such problem. Attached patch should fix the
issue. You can try this patch to see if it fixes the issue for you.I'm afraid I'm not able to reproduce the problem which patch should fix.
What I did (today's master, without patch):
autovacuum off
pgbench -i -s 100select relname, relpages, reltuples from pg_class where relname =
'pgbench_accounts';
relname | relpages | reltuples
------------------+----------+-----------
pgbench_accounts | 163935 | 1e+07vacuum full pgbench_accounts;
# select relname, relpages, reltuples from pg_class where relname =
'pgbench_accounts';
relname | relpages | reltuples
------------------+----------+-----------
pgbench_accounts | 163935 | 1e+07I've tried to add hash index to that table and print notice about number of
pages and tuples immediately after estimate_rel_size() in hashbuild(). hash
index got right estimation even I deleted all rows before vacuum full. What
am I doing wrong?Patch looks good except, seems, updating stats is better to move to
swap_relation_files(), then it will work even for toast tables.
I haven't looked into the patch properly, but, i could reproduce the
issue. Here are the steps that i am following,
CREATE TABLE hash_index_table (keycol INT);
INSERT INTO hash_index_table (keycol) SELECT (a - 1) % 1000 + 1 FROM
GENERATE_SERIES(1, 1000000) a;
CREATE INDEX CONCURRENTLY hash_index on hash_index_table USING HASH
(keycol) with (fillfactor = 80);
CREATE EXTENSION pgstattuple;
select oid, relname, relpages, reltuples from pg_class where relname =
'hash_index';
select relname, relpages, reltuples from pg_class where relname =
'hash_index_table';
select * from pgstathashindex('hash_index');
DROP INDEX hash_index;
CREATE INDEX CONCURRENTLY hash_index on hash_index_table USING HASH
(keycol) with (fillfactor = 100);
select * from pgstathashindex('hash_index');
select oid, relname, relpages, reltuples from pg_class where relname =
'hash_index';
select relname, relpages, reltuples from pg_class where relname =
'hash_index_table';
VACUUM FULL;
select * from pgstathashindex('hash_index');
select oid, relname, relpages, reltuples from pg_class where relname =
'hash_index';
select relname, relpages, reltuples from pg_class where relname =
'hash_index_table';
I think the issue is only visible when VACUUM FULL is executed after
altering the index table fill-factor. Could you please try with above
steps and let us know your observations. Thanks.
With patch, I could see that the index table stats before and after
VACUUM FULL are same.
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
On Sat, Dec 16, 2017 at 8:03 AM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
Hi,
On Fri, Dec 15, 2017 at 8:08 PM, Teodor Sigaev <teodor@sigaev.ru> wrote:
Hi!
I think if we update the stats in copy_heap_data after copying the
data, then we don't see such problem. Attached patch should fix the
issue. You can try this patch to see if it fixes the issue for you.I'm afraid I'm not able to reproduce the problem which patch should fix.
What I did (today's master, without patch):
autovacuum off
pgbench -i -s 100select relname, relpages, reltuples from pg_class where relname =
'pgbench_accounts';
relname | relpages | reltuples
------------------+----------+-----------
pgbench_accounts | 163935 | 1e+07vacuum full pgbench_accounts;
# select relname, relpages, reltuples from pg_class where relname =
'pgbench_accounts';
relname | relpages | reltuples
------------------+----------+-----------
pgbench_accounts | 163935 | 1e+07I've tried to add hash index to that table and print notice about number of
pages and tuples immediately after estimate_rel_size() in hashbuild(). hash
index got right estimation even I deleted all rows before vacuum full. What
am I doing wrong?Patch looks good except, seems, updating stats is better to move to
swap_relation_files(), then it will work even for toast tables.I haven't looked into the patch properly, but, i could reproduce the
issue. Here are the steps that i am following,CREATE TABLE hash_index_table (keycol INT);
INSERT INTO hash_index_table (keycol) SELECT (a - 1) % 1000 + 1 FROM
GENERATE_SERIES(1, 1000000) a;CREATE INDEX CONCURRENTLY hash_index on hash_index_table USING HASH
(keycol) with (fillfactor = 80);CREATE EXTENSION pgstattuple;
select oid, relname, relpages, reltuples from pg_class where relname =
'hash_index';select relname, relpages, reltuples from pg_class where relname =
'hash_index_table';select * from pgstathashindex('hash_index');
DROP INDEX hash_index;
CREATE INDEX CONCURRENTLY hash_index on hash_index_table USING HASH
(keycol) with (fillfactor = 100);select * from pgstathashindex('hash_index');
select oid, relname, relpages, reltuples from pg_class where relname =
'hash_index';select relname, relpages, reltuples from pg_class where relname =
'hash_index_table';VACUUM FULL;
select * from pgstathashindex('hash_index');
select oid, relname, relpages, reltuples from pg_class where relname =
'hash_index';select relname, relpages, reltuples from pg_class where relname =
'hash_index_table';I think the issue is only visible when VACUUM FULL is executed after
altering the index table fill-factor. Could you please try with above
steps and let us know your observations. Thanks.With patch, I could see that the index table stats before and after
VACUUM FULL are same.
I think you should have shared the value of stats before and after
patch so that we can see if the above is a right way to validate.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com