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

On Sat, Dec 16, 2017 at 8:03 AM, Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
> Hi,
>
> On Fri, Dec 15, 2017 at 8:08 PM, Teodor Sigaev <teodor(at)sigaev(dot)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 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