On Tue, May 3, 2016 at 1:35 PM, Maxim Boguk <maxim.bo...@gmail.com> wrote:
> > > On Tue, May 3, 2016 at 8:09 PM, Maxim Boguk <maxim.bo...@gmail.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.bo...@gmail.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." > >