Search Postgresql Archives

Re: Insert only table and size of GIN index JSONB field.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Tue, May 3, 2016 at 1:35 PM, Maxim Boguk <maxim.boguk@xxxxxxxxx> wrote:


On Tue, May 3, 2016 at 8:09 PM, Maxim Boguk <maxim.boguk@xxxxxxxxx> 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.boguk@xxxxxxxxx

"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."




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux