Re: 10.1: hash index size exploding on vacuum full analyze
От | Ashutosh Sharma |
---|---|
Тема | Re: 10.1: hash index size exploding on vacuum full analyze |
Дата | |
Msg-id | CAE9k0P=ihFyPAKfrMX9GaDo5RaeGSJ4i4nb28fGev15wKOPYog@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: 10.1: hash index size exploding on vacuum full analyze (Teodor Sigaev <teodor@sigaev.ru>) |
Ответы |
Re: 10.1: hash index size exploding on vacuum full analyze
(Amit Kapila <amit.kapila16@gmail.com>)
|
Список | pgsql-bugs |
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 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: pg_restore --create --no-tablespaces should not issue 'CREATEDATABASE ... TABLESPACE'
Следующее
От: Amit KapilaДата:
Сообщение: Re: 10.1: hash index size exploding on vacuum full analyze