On Tue, May 20, 2014 at 4:50 AM, Alexander Korotkov <aekorot...@gmail.com>wrote:
> I found that sometimes larger maintenance_work_mem leads to larger GIN > index. That is quite strange. ISTM that it's related to posting lists > compression but I can't figure out how exactly it is. > It appears to be not related to posting lists compression. I did reproduce it on 9.2. create table test as (select array[(random()*10000000)::int]::int[] as v from generate_series(1,10000000) g); set maintenance_work_mem = '2GB'; create index test_idx1 on test using gin(v); set maintenance_work_mem = '16MB'; create index test_idx2 on test using gin(v); Schema | Name | Type | Owner | Table | Size | Description --------+----------------------------+-------+----------+-------------+---------+------------- public | test_idx1 | index | smagen | test | 392 MB | public | test_idx2 | index | smagen | test | 268 MB | (2 rows) The reason of it is that we filling entry tree with inserting without any special optimization. Large maintenance_work_mem gives us ascending order of insertion. Thus insertion is performed always into rightmost page leaving rest of pages half-empty. Small maintenance_work_mem gives us more random order of insertion. Such insertions makes pages about 75% filled in average. Posting trees has special optimization for this case while entry tree doesn't. ------ With best regards, Alexander Korotkov.
