Avoid overhead open-close indexes (catalog updates)

From: Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Avoid overhead open-close indexes (catalog updates)
Date: 2022-08-31 11:16:55
Message-ID: CAEudQAqh0F9y6Di_Wc8xW4zkWm_5SDd-nRfVsCn=h0Nm1C_mrg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-avoid-overhead-open-close-indexes.patch text/x-patch 7.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christoph Berg 2022-08-31 11:55:19 Re: plpgsql-trigger.html: Format TG_ variables as table (patch)
Previous Message Amit Langote 2022-08-31 11:01:13 Re: SQL/JSON features for v15