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

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.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com