Re: Fillfactor for GIN indexes

On 07/10/2015 01:13 PM, Alexander Korotkov wrote:
> On Fri, Jul 10, 2015 at 4:54 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
> wrote:
>> + #define GIN_MIN_FILLFACTOR 20
>> + #define GIN_DEFAULT_FILLFACTOR 90
>> I am still worrying about using a default fillfactor at 90, as we did a
>> lot of promotion about compression of GIN indexes in 9.4. Feel free to
>> ignore this comment if you think that 90 is a good default. The difference
>> is visibly in order of MBs even for large indexes still, this is changing
>> the default of 9.4 and 9.5.
>
> On the other side, it's unclear why should we have fillfactor distinct from
> btree..

Good question. One argument is because the items on a GIN posting page
are much smaller (2-3 bytes typically) than index tuples in a B-tree
page (32 bytes or more). By a rough calculation, in the 10% free space
you leave in an index page, which is about 800 bytes, you can insert at
most 25 or so tuples. In the same amount of free space in a GIN page,
you can fit hundreds items. The fillfactor is particularly useful to
avoid splitting a lot pages after creating a new index, when you do a
few random updates.

Another argument is that for the typical use cases of GIN, tuples are
not updated as often as with B-tree indexes.

A third argument is that before this patch, we always packed the index
as full as possible (i.e. fillfactor=100), and we want to avoid
"changing the default" so much.

I'm not sure any of those arguments are very strong, but my gut feeling
is that 90 might indeed be too small. Perhaps set the default to 95..

I'm curious why the minimum in the patch is 20, while the minimum for
b-tree is 10, though. I don't see any particularly good reason for that.

- Heikki