Avoid overhead open-close indexes (catalog updates)

Ranier Vilela <ranier.vf@gmail.com>

From: Ranier Vilela <ranier.vf@gmail.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2022-08-31T11:16:55Z
Lists: pgsql-hackers

Attachments

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.

head:

1. REINDEX TABLE CONCURRENTLY pgbench_accounts;
Time: 77,805 ms
Time: 74,836 ms
Time: 73,480 ms

2. REINDEX TABLE CONCURRENTLY pgbench_tellers;
Time: 22,260 ms
Time: 22,205 ms
Time: 21,008 ms

patched:

1. REINDEX TABLE CONCURRENTLY pgbench_accounts;
Time: 65,048 ms
Time: 61,853 ms
Time: 61,119 ms

2. REINDEX TABLE CONCURRENTLY pgbench_tellers;
Time: 15,999 ms
Time: 15,961 ms
Time: 13,264 ms

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

regards,
Ranier Vilela

Commits

  1. Use multi-inserts for pg_ts_config_map

  2. Use multi-inserts for pg_enum

  3. Avoid some overhead with open and close of catalog indexes

  4. Preserve index data in pg_statistic across REINDEX CONCURRENTLY