Re: Fillfactor for GIN indexes

On Thu, Jan 8, 2015 at 12:31 AM, Alexander Korotkov <aekorotkov(at)gmail(dot)com>
wrote:
>
> Rewritten version of patch is attached. I made following changes:
>
> 1) I removed fillfactor handling from entry tree. Because in this case
> fillfactor parameter would be only upper bound for actual page fullness.
> It's very like GiST approach to fillfactor. But I would rather remove
> fillfactor from GiST than spread such approach to other AMs.
> 2) Fillfactor handling for posting trees is fixed.
> 3) Default fillfactor for GIN is reverted to 90. I didn't mean that
> default fillfactor should be 75%. I mean that equilibrium state of tree
> would be about 75% fullness. But that doesn't mean that we don't want
> indexes to be better packed just after creation. Anyway I didn't see reason
> why default fillfactor for GIN btree should differs from default fillfactor
> of regular btree.
>

Just forgot simple case that I used to test the patch.

create table test as (select array[1,2,3] v from
generate_series(1,1000000));
create index test_idx20 on test using gin(v) with (fillfactor=20);
create index test_idx30 on test using gin(v) with (fillfactor=30);
create index test_idx40 on test using gin(v) with (fillfactor=40);
create index test_idx50 on test using gin(v) with (fillfactor=50);
create index test_idx60 on test using gin(v) with (fillfactor=60);
create index test_idx70 on test using gin(v) with (fillfactor=70);
create index test_idx80 on test using gin(v) with (fillfactor=80);
create index test_idx90 on test using gin(v) with (fillfactor=90);
create index test_idx100 on test using gin(v) with (fillfactor=100);

List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+--------+-------+---------+-------------
public | test_idx20 | index | smagen | test | 16 MB |
public | test_idx30 | index | smagen | test | 11 MB |
public | test_idx40 | index | smagen | test | 8152 kB |
public | test_idx50 | index | smagen | test | 6520 kB |
public | test_idx60 | index | smagen | test | 5176 kB |
public | test_idx70 | index | smagen | test | 4480 kB |
public | test_idx80 | index | smagen | test | 3928 kB |
public | test_idx90 | index | smagen | test | 3520 kB |
public | test_idx100 | index | smagen | test | 3184 kB |
(9 rows)

------
With best regards,
Alexander Korotkov.