Re: Fillfactor for GIN indexes

On Tue, Jul 21, 2015 at 2:49 PM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

> 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.

Yeah, it's likely because of 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...

Probably, but currently we are in quite unlogical situation. We have
default fillfactor = 90 for GiST where it has no use cases at all and
effectively is just a waste of space. On the other had we're rejecting
fillfactor for GIN where it could have at least some use cases... Should we
don't have fillfactor for both GiST and GIN?

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company