From: | Alexander Korotkov <aekorotkov(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
Subject: | Re: Negative imact of maintenance_work_mem to GIN size |
Date: | 2014-05-20 10:56:09 |
Message-ID: | CAPpHfdsQRkPTLBe4=P9Xnh9UDKvCt_8sB6e=fWC4CHfvhMvoeQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, May 20, 2014 at 4:50 AM, Alexander Korotkov <aekorotkov(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-05-20 11:09:20 | Re: buildfarm animals and 'snapshot too old' |
Previous Message | David Rowley | 2014-05-20 09:44:38 | Re: Allowing join removals for more join types |