Re: non-HOT update not looking at FSM for large tuple update

On Thu, Mar 18, 2021 at 5:30 PM Matthias van de Meent <
boekewurm+postgres(at)gmail(dot)com> wrote:
>
> > + * The minimum space on a page for it to be considered "empty"
regardless
> > + * of fillfactor. We base this on MaxHeapTupleSize, minus a small
amount
> > + * of slack. We allow slack equal to 1/8 the maximum space that
could be
> > + * taken by line pointers, which is somewhat arbitrary.
>
> > + * We want to allow inserting a large tuple into an empty page
even if
> > + * that would violate the fillfactor. Otherwise, we would
unnecessarily
> > + * extend the relation. Instead, ask the FSM for
maxPaddedFsmRequest
> > + * bytes. This will allow it to return a page that is not quite
empty
> > + * because of unused line pointers
>
> How about
>
> + * Because pages that have no items left can still have space
allocated
> + * to item pointers, we consider pages "empty" for FSM requests when
they
> + * have at most 1/8 of their MaxHeapTuplesPerPage item pointers' space
> + * allocated. This is a somewhat arbitrary number, but should prevent
> + * most unnecessary relation extensions due to not finding "empty"
pages
> + * while inserting combinations of large tuples with low fillfactors.
>
> + * When the free space to be requested from the FSM is greater than
> + * maxPaddedFsmRequest, this is considered equivalent to requesting
> + * insertion on an "empty" page, so instead of failing to find a page
> + * with more empty space than an "empty" page and extend the relation,
> + * we try to find a page which is considered "emtpy".
>
> This is slightly more verbose, but I think this clarifies the
> reasoning why we need this a bit better. Feel free to reject or adapt
> as needed.

I like this in general, but still has some rough edges. I've made another
attempt in v5 incorporating your suggestions. Let me know what you think.

--
John Naylor
EDB: http://www.enterprisedb.com