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

Hi,

On Sat, Dec 16, 2017 at 8:34 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> 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.
>>>

Here are the stats i saw before and after VACUUM FULL - with and without the patch. Please note i have followed the steps shared in - [1].

A) Without patch - Stats before and after VACUUM FULL:

postgres[43768]=# select * from pgstathashindex('hash_index');
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
| version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent   |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
|       4 |         2560 |           2024 |            1 |            0 |    1000000 |          0 | 46.4793701521013 |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
(1 row)

Time: 6.531 ms

postgres[43768]=# select oid, relname, relpages, reltuples from pg_class where relname = 'hash_index';
+-------+------------+----------+-----------+
|  oid  |  relname   | relpages | reltuples |
+-------+------------+----------+-----------+
| 16398 | hash_index |     4586 |     1e+06 |
+-------+------------+----------+-----------+
(1 row)

Time: 0.369 ms
postgres[43768]=# VACUUM FULL;
VACUUM
Time: 4145.813 ms (00:04.146)

postgres[43768]=# select * from pgstathashindex('hash_index');
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
| version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent   |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
|       4 |         3072 |           2019 |            1 |            0 |    1000000 |          0 | 51.8093562713087 |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
(1 row)

Time: 9.194 ms

postgres[43768]=# select oid, relname, relpages, reltuples from pg_class where relname = 'hash_index';
+-------+------------+----------+-----------+
|  oid  |  relname   | relpages | reltuples |
+-------+------------+----------+-----------+
| 16398 | hash_index |     5093 |     1e+06 |
+-------+------------+----------+-----------+
(1 row)

Time: 1.289 ms

B) With Patch- Stats before and after VACUUM FULL:

postgres[31111]=# select * from pgstathashindex('hash_index');
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
| version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent   |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
|       4 |         2560 |           2024 |            1 |            0 |    1000000 |          0 | 46.4793701521013 |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
(1 row)

Time: 6.539 ms

postgres[31111]=# select oid, relname, relpages, reltuples from pg_class where relname = 'hash_index';
+-------+------------+----------+-----------+
|  oid  |  relname   | relpages | reltuples |
+-------+------------+----------+-----------+
| 16398 | hash_index |     4586 |     1e+06 |
+-------+------------+----------+-----------+
(1 row)

Time: 0.379 ms
 
postgres[31111]=# VACUUM FULL;
VACUUM
Time: 4265.662 ms (00:04.266)

postgres[31111]=# select * from pgstathashindex('hash_index');
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
| version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent   |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
|       4 |         2560 |           2024 |            1 |            0 |    1000000 |          0 | 46.4793701521013 |
+---------+--------------+----------------+--------------+--------------+------------+------------+------------------+
(1 row)

Time: 6.699 ms
 
postgres[31111]=# select oid, relname, relpages, reltuples from pg_class where relname = 'hash_index';
+-------+------------+----------+-----------+
|  oid  |  relname   | relpages | reltuples |
+-------+------------+----------+-----------+
| 16398 | hash_index |     4586 |     1e+06 |
+-------+------------+----------+-----------+
(1 row)

Time: 0.893 ms

[1] - https://www.postgresql.org/message-id/CAE9k0P%3DihFyPAKfrMX9GaDo5RaeGSJ4i4nb28fGev15wKOPYog%40mail.gmail.com

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