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 |
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 |