Re: A strange GiST error message or fillfactor of GiST build

On Sat, Sep 1, 2018 at 6:03 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Aug 29, 2018 at 4:32 AM, Kyotaro HORIGUCHI
> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> > After the attached patch applied, the above messages becomes as
> > follows. (And index can be built being a bit sparse by fill
> > factor.)
> >
> >> ERROR: index row size 8016 exceeds maximum 7333 for index "y_cube_idx"
> >
> > I'm not sure why 277807bd9e didn't do that completely so I may be
> > missing something. Is there any thoughts?
>
> It seems strange to me that we consider respecting the fillfactor to
> be more important than letting the operation succeed. I would have
> thought that the fillfactor would not apply when placing a tuple into
> a completely empty page. The point of the setting is, of course, to
> leave some free space available on the page for future tuples, but if
> the tuples are big enough that only one fits in a page anyway, that's
> pointless.

IIRC, I've already wrote that I think we don't need GiST fillfactor
parameter at all. As you pointed, the purpose of fillfactor parameter
is to leave some free space in the pages. That, in turn, allow us to
evade the flood of page splits, which may happen when you start
insertions into freshly build and perfectly packed index. But thats
makes sense only for index building algorithms, which can pack index
pages as tight as possible. Our B-tree build algorithm is one of such
alogirhtms: at first it sorts tuples and then packs them into pages as
tight as required. But GiST is another story: GiST index build in the
pretty same as insertion tuples one by one. Yes, we have some bulk
insert optimization for GiST, but it optimizes only IO and internally
still uses picksplit. So, GiST indexes are never perfectly packed
even with fillfactor = 100. Why should we bother setting lower
fillfactor?

Thus, I would vote for removing GiST fillfactor altogether. Assuming
we can't do this for compatibility reasons, I would vote for setting
default GiST fillfactor to 100, and don't introduce new places where
we take it into account.

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