From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
---|---|
To: | "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 10:35:36 |
Message-ID: | CAK-MWwQCccN=mzrP1_eKw-KAg-Schn4bUkBiu3Zfs8-Lafn9LA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
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 | dandl | 2016-05-03 11:22:45 | Does the initial postgres user have a password? |
Previous Message | Bill Moran | 2016-05-03 10:33:32 | Re: (VERY) Slow Query - PostgreSQL 9.2 |