Re: Avoid overhead open-close indexes (catalog updates)

At Wed, 31 Aug 2022 08:16:55 -0300, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> wrote in
> Hi,
>
> The commit
> https://github.com/postgres/postgres/commit/b17ff07aa3eb142d2cde2ea00e4a4e8f63686f96
> Introduced the CopyStatistics function.
>
> To do the work, CopyStatistics uses a less efficient function
> to update/insert tuples at catalog systems.
>
> The comment at indexing.c says:
> "Avoid using it for multiple tuples, since opening the indexes
> * and building the index info structures is moderately expensive.
> * (Use CatalogTupleInsertWithInfo in such cases.)"
>
> So inspired by the comment, changed in some fews places,
> the CatalogInsert/CatalogUpdate to more efficient functions
> CatalogInsertWithInfo/CatalogUpdateWithInfo.
>
> With quick tests, resulting in small performance.

Considering the whole operation usually takes far longer time, I'm not
sure that amount of performance gain is useful or not, but I like the
change as a matter of tidiness or as example for later codes.

> There are other places that this could be useful,
> but a careful analysis is necessary.

What kind of concern do have in your mind?

By the way, there is another similar function
CatalogTupleMultiInsertWithInfo() which would be more time-efficient
(but not space-efficient), which is used in InsertPgAttributeTuples. I
don't see a clear criteria of choosing which one of the two, though.

I think the overhead of catalog index open is significant when any
other time-consuming tasks are not involved in the whole operation.
In that sense, in term of performance, rather storeOperations and
storePrecedures (called under DefineOpCalss) might get more benefit
from that if disregarding the rareness of the command being used..

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center