On Tue, May 20, 2014 at 4:50 AM, Alexander Korotkov <aekorotkov@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.