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
- 0001-avoid-overhead-open-close-indexes.patch (text/x-patch) patch 0001
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
-
Use multi-inserts for pg_ts_config_map
- 63c833f4bdca 16.0 landed
-
Use multi-inserts for pg_enum
- 1ff416121884 16.0 landed
-
Avoid some overhead with open and close of catalog indexes
- 09a72188cd5c 16.0 landed
-
Preserve index data in pg_statistic across REINDEX CONCURRENTLY
- b17ff07aa3eb 14.0 cited