| From: | Floris Van Nee <florisvannee(at)Optiver(dot)com> |
|---|---|
| To: | feichanghong <feichanghong(at)qq(dot)com> |
| Cc: | "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | RE: temp table on commit delete rows performance issue |
| Date: | 2024-07-18 13:36:12 |
| Message-ID: | d2784ba7b95a4c3ca382115592947822@Optiver.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
> I also encountered the similar performance issue with temporary tables
> andprovided a patch to optimize the truncate performance during commit
> in [1].
Interesting, that is definitely another good way to improve the performance,
especially with a large number of temp tables. I think the two optimizations
can actually work well together.
Your optimization on only truncating the tables that are actually used.
Combined with a patch like attached which makes sure that no WAL is generated at all
for the ON COMMIT DELETE ROWS operation.
On my test system this reduces WAL generation for the pgbench test case
I posted previously to 0 (and therefore brought WAL replay process CPU usage
from 100% CPU and lagging behind to only 0% CPU usage)
-Floris
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Remove-any-WAL-logging-for-ON-COMMIT-DELETE-ROWS.patch | application/octet-stream | 7.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fujii Masao | 2024-07-18 13:47:16 | Re: Add a GUC check hook to ensure summarize_wal cannot be enabled when wal_level is minimal |
| Previous Message | Nazir Bilal Yavuz | 2024-07-18 12:55:23 | Re: CI, macports, darwin version problems |