Re: Fillfactor for GIN indexes - Mailing list pgsql-hackers

From Alexander Korotkov
Subject Re: Fillfactor for GIN indexes
Date
Msg-id CAPpHfduV=y1d0W-d4b5T+Af_zgatcXi5iMoQT-Jjkry70qRCWw@mail.gmail.com
Whole thread Raw
In response to Re: Fillfactor for GIN indexes  (Heikki Linnakangas <hlinnaka@iki.fi>)
List pgsql-hackers
On Tue, Jul 21, 2015 at 7:20 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
On 07/21/2015 04:14 PM, Alexander Korotkov wrote:
On Tue, Jul 21, 2015 at 3:52 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:

On 07/21/2015 02:56 PM, Alexander Korotkov wrote:

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.


Why is it useless for GiST?


It's because all of GiST pages are produced by page splits. So, just after
CREATE INDEX GiST pages aren't tightly packed in average. Actually, they
could be tightly packed by incredible coincidence, but for large indexes
it's quite safe assumption that they are not. With GiST we don't have storm
of page splits after index creation with fillfactor = 100. So, why should
we reserve additional space with fillfactor = 90?

Aha, I see. Yeah, that's pretty useless. Ideally, we would make the GiST build algorithm smarter so that it would pack the pages more tightly. I have no idea how to do that, however.

Anyway, the fact that fillfactor is useless for GiST is more of an argument for removing it from GiST, than for adding it to GIN.

Yes it is. I've just think that fillfactor is more useful for GIN than for GiST now. However, it's probably doesn't worth it for both of them.
One of our customers complains that freshly built GIN indexes get bloat very fast. I've asked them a test case. Using that test case I would try to see if fillfactor for GIN could help in practice. For now we can mark it as "Returned with feedback" where feedback is "Real life use cases needed".
Removing fillfactor for GiST is now not easy. Once it's exposed for users, removing it completely would break compatibility. I would propose to chage default fillfactor for GiST from 90 to 100.

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

pgsql-hackers by date:

Previous
From: Fabien COELHO
Date:
Subject: Re: pgbench stats per script & other stuff
Next
From: Tom Lane
Date:
Subject: What is HeapScanDescData.rs_initblock good for?