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