From: | Oleg Bartunov <obartunov(at)gmail(dot)com> |
---|---|
To: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Insert only table and size of GIN index JSONB field. |
Date: | 2016-05-03 13:50:18 |
Message-ID: | CAF4Au4wAEjx79zRrnk-kvwOxGuTWi_8bwwy1bSWimqCn=Cr8aQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, May 3, 2016 at 1:35 PM, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
>
>
> On Tue, May 3, 2016 at 8:09 PM, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> I started with empty table with index over
>> custom_fields | jsonb
>> field
>> defined as:
>> "idx_learners_custom_fields" gin (custom_fields)
>> Globally gin_pending_list_limit set to 2MB.
>> Database version is 9.5.2.
>>
>> Now question:
>> If table populated with 1M records in single transaction then the final
>> size of the GIN index is:
>> 4265 MB
>> but after I performed reindex index idx_learners_custom_fields;
>> the index size had been reduced 15x to 295 MB.
>>
>> Is this behavior expected?
>>
>
> It's look easy to reproduce.
> I can send a sample dataset for analyze.
>
sample data are always welcome !
>
> drop table if exists test;
> create table test (custom_fields jsonb);
> create index test_gin_key on test USING GIN(custom_fields);
> insert into test select custom_fields from public.learners;
> INSERT 0 1000000
> \di+ test_gin_key
> List of relations
> Schema | Name | Type | Owner | Table | Size | Description
> --------+--------------+-------+----------+-------+---------+-------------
> public | test_gin_key | index | postgres | test | 4211 MB |
>
> reindex index test_gin_key;
>
> \di+ test_gin_key
> List of relations
> Schema | Name | Type | Owner | Table | Size | Description
> --------+--------------+-------+----------+-------+--------+-------------
> public | test_gin_key | index | postgres | test | 295 MB |
>
>
>
>
>
> --
> Maxim Boguk
> Senior Postgresql DBA
> http://www.postgresql-consulting.com/
>
> Phone RU: +7 910 405 4718
> Phone AU: +61 45 218 5678
>
> LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
> Skype: maxim.boguk
> Jabber: maxim(dot)boguk(at)gmail(dot)com
>
> "People problems are solved with people.
> If people cannot solve the problem, try technology.
> People will then wish they'd listened at the first stage."
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-05-03 13:50:36 | Re: Field size become unlimited in union... |
Previous Message | Oleg Bartunov | 2016-05-03 13:47:51 | Re: postgresql & Fulltext & ranking & my own functions |