Re: Fillfactor for GIN indexes

Hmm. That's slightly different from the test case I used, in that the update is changing the indexed value, which means that the new value goes to different posting tree than the old one. I'm not sure if that makes any difference. You're also updating more rows, 1/50 vs. 1/100.

This case is closer to my earlier one:

postgres=# create table foo (id int4, i int4, t text) with (fillfactor=90);
CREATE TABLE
postgres=# insert into foo select g, 1 from  generate_series(1, 1000000) g;
INSERT 0 1000000
postgres=# create index btree_foo_id on foo (id); -- to force non-HOT updates
CREATE INDEX
postgres=# create index gin_i on foo using gin (i) with (fastupdate=off);
CREATE INDEX
postgres=# \di+
                           List of relations
 Schema |     Name     | Type  | Owner  | Table |  Size   | Description
--------+--------------+-------+--------+-------+---------+-------------
 public | btree_foo_id | index | heikki | foo   | 21 MB   |
 public | gin_i        | index | heikki | foo   | 1072 kB |
(2 rows)

postgres=# update foo set id=-id where id % 100 = 0;
UPDATE 10000
postgres=# \di+
                           List of relations
 Schema |     Name     | Type  | Owner  | Table |  Size   | Description
--------+--------------+-------+--------+-------+---------+-------------
 public | btree_foo_id | index | heikki | foo   | 22 MB   |
 public | gin_i        | index | heikki | foo   | 1080 kB |
(2 rows)

I'm not sure what's making the difference to your test case. Could be simply that your case happens to leave less free space on each page, because of the different data.

Based on this, I think we should just drop this patch. It's not useful in
practice.

I wouldn't say it's not useful at all. It's for sure not as useful as btree
fillfactor, but it still could be useful in some cases...
Probably, we could leave 100 as default fillfactor, but provide an option.

Doesn't seem worth the trouble to me...