Re: Fillfactor for GIN indexes

On 25.2.2015 10:20, Alexander Korotkov wrote:
> On Tue, Feb 24, 2015 at 5:15 PM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)com>> wrote:
>
>> I hoped lowering the fillfactor will improve this, but
>> fillfactor=75 had pretty much no effect in this case. Is that
>> expected for this kind of workload? I see the previous discussion
>> talked about random updates, not inserts, so maybe that's the
>> culprit?
>
>
> Yes. Since posting trees are ordered by item pointers, you can get
> benefit of fillfactor only if you use some item pointers lower than
> item pointers already in use. You can still get benefit in the insert
> case but you should have already some free space in the heap (perhaps
> do some deletes and vacuum).

OK, understood. Thanks for the explanation.

> Actually, this is narrowing benefit from GIN fillfactor. Probably,
> that means that we should still have default value of 100. But I
> think GIN fillfactor still might be useful.

I'm not sure about that. It's true that lowering fillfactor to 90 does
not help this particular workload, but it does not hurt it either. For
other common workloads (updating the values, not just inserting them),
lowering the fillfactor to 90 may easily be a big improvement.

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services