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

On Tue, Dec 19, 2017 at 5:30 PM, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
> Hi,
>
> On Sat, Dec 16, 2017 at 12:56 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>
>> On Sat, Dec 16, 2017 at 12:27 PM, AP <pgsql@inml.weebeastie.net> wrote:
>> > On Sat, Dec 16, 2017 at 09:08:23AM +0530, Amit Kapila wrote:
>> >> On Fri, Dec 15, 2017 at 8:08 PM, Teodor Sigaev <teodor@sigaev.ru> wrote:
>> >> The estimation depends on the type of columns and stats.  I think we
>> >> need to use schema and stats in the way AP is using to see the effect
>> >> AP is seeing.  I was under impression that AP will help us in
>> >> verifying the problem as he can reproduce it, but it seems he is busy.
>> >
>> > Different fires keep springing up and they are causing delay. This
>> > is still on my mind and I'll get back to it as soon as I can.
>> >
>>
>> Okay.  I think Ashutosh has reproduced it with a standalone test, let
>> us see if that suffice the need.  In any case, feel free to verify in
>> the meantime.
>
> I am able to reproduce the problem reported by AP with the following
> test-case. My earlier test-case-[1] was also helpful in diagnosing the
> problem and understanding Amit's patch -[2] but this test-case exposes
> the problem more than my earlier test-case.
>
> test-case
> =======
> 1) CREATE TABLE hash_index_table(datum_id BYTEA NOT NULL);
> 2) INSERT INTO hash_index_table SELECT repeat(stringu1, 30)::bytea FROM tenk1;
>
> 3) ALTER TABLE hash_index_table ALTER COLUMN datum_id SET STATISTICS 0;
>
> 4) ANALYZE hash_index_table;
>
> 5) CREATE INDEX hash_index ON hash_index_table USING hash (datum_id);
>
> 6) select oid, relname, relpages, reltuples from pg_class where
> relname = 'hash_index';
>
> 7) DROP INDEX hash_index;
>
> 8) CREATE INDEX hash_index ON hash_index_table USING hash (datum_id);
>
> 9) select oid, relname, relpages, reltuples from pg_class where
> relname = 'hash_index';
>
> 10) VACUUM FULL hash_index_table;
>
> 11) select oid, relname, relpages, reltuples from pg_class where
> relname = 'hash_index';
>
> The output of above two test-cases without patch is as follows,
>
> Output (without patch):
> ================
> postgres[72965]=# select oid, relname, relpages, reltuples from
> pg_class where relname = 'hash_index';
> +--------+------------+----------+-----------+
> |  oid   |  relname   | relpages | reltuples |
> +--------+------------+----------+-----------+
> | 287169 | hash_index |       69 |     10000 |
> +--------+------------+----------+-----------+
> (1 row)
>
> Time: 0.381 ms
> postgres[72965]=#
> postgres[72965]=# VACUUM FULL hash_index_table;
> VACUUM
> Time: 55.703 ms
> postgres[72965]=#
> postgres[72965]=# select oid, relname, relpages, reltuples from
> pg_class where relname = 'hash_index';
> +--------+------------+----------+-----------+
> |  oid   |  relname   | relpages | reltuples |
> +--------+------------+----------+-----------+
> | 287169 | hash_index |      130 |     10000 |
> +--------+------------+----------+-----------+
> (1 row)
>
> Time: 0.904 ms
>
> Output (with patch):
> ==============
> postgres[85460]=# select oid, relname, relpages, reltuples from
> pg_class where relname = 'hash_index';
> +-------+------------+----------+-----------+
> |  oid  |  relname   | relpages | reltuples |
> +-------+------------+----------+-----------+
> | 26394 | hash_index |       69 |     10000 |
> +-------+------------+----------+-----------+
> (1 row)
>
> Time: 0.370 ms
> postgres[85460]=#
> postgres[85460]=# VACUUM FULL hash_index_table;
> VACUUM
> Time: 68.351 ms
> postgres[85460]=#
> postgres[85460]=# select oid, relname, relpages, reltuples from
> pg_class where relname = 'hash_index';
> +-------+------------+----------+-----------+
> |  oid  |  relname   | relpages | reltuples |
> +-------+------------+----------+-----------+
> | 26394 | hash_index |       69 |     10000 |
> +-------+------------+----------+-----------+
> (1 row)
>
> Time: 0.838 ms
>
> Please note that i have tried running above test-case both with and
> without ANALYZE hash_index_table (step #4) and the problem is observed
> in both the cases.
>
> [1]- https://www.postgresql.org/message-id/CAE9k0P%3DihFyPAKfrMX9GaDo5RaeGSJ4i4nb28fGev15wKOPYog%40mail.gmail.com
>
> [2]- https://www.postgresql.org/message-id/CAA4eK1%2B6BSGrm%2BNtUDhx59CNR51Ehbnmch9LpswMEyrLBBjKLg%40mail.gmail.com
>

I'm not sure when AP is planning to share his test-results. But, I've
shared mine test-results -[1] and also reviewed the patch. As
mentioned in my earlier update -[1], the patch looks good to me and it
fixes the issue. I am therefore moving the patch to 'Ready for
Committer'. Thanks.

[1]-https://www.postgresql.org/message-id/CAE9k0PmTZKLA2hKAPT6OCinH%2BXX%2BXVSej3jx17j9SMBJr%3DFvkA%40mail.gmail.com

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

>
>>
>>
>> --
>> With Regards,
>> Amit Kapila.
>> EnterpriseDB: http://www.enterprisedb.com
>>