Hi wenhui,
Thank you for your suggestions. I have supplemented some performance tests. Here is the TPS performance data for different numbers of temporary tables under different thresholds, as compared with the head (98347b5a). The testing tool used is pgbench, with the workload being to insert into one temporary table (when the number of temporary tables is 0, the workload is SELECT 1): | table num | 0 | 1 | 5 | 10 | 100 | 1000 | |---------------|--------------|--------------|-------------|-------------|-------------|-------------| | head 98347b5a | 39912.722209 | 10064.306268 | 7452.071689 | 5641.487369 | 1073.203851 | 114.530958 | | threshold 1 | 40332.367414 | 7078.117192 | 7044.951156 | 7020.249434 | 6893.652062 | 5826.597260 | | threshold 5 | 40173.562744 | 10017.532933 | 7023.770203 | 7024.283577 | 6919.769315 | 5806.314494 | Here is the TPS performance data for different numbers of temporary tables at a threshold of 5, compared with the head (commit 98347b5a). The testing tool is pgbench, with the workload being to insert into all temporary tables: | table num | 1 | 5 | 10 | 100 | 1000 | |---------------|-------------|-------------|-------------|------------|-----------| | head 98347b5a | 7243.945042 | 3627.290594 | 2262.594766 | 297.856756 | 27.745808 | | threshold 5 | 7287.764656 | 3130.814888 | 2038.308763 | 288.226032 | 27.705149 | According to test results, the patch does cause some performance loss with fewer temporary tables, but benefits are substantial when many temporary tables are used. The specific threshold could be set to 10 (HDDs may require a smaller one). I've provided two patches in the attachments, both with a default threshold of 10. One has the threshold configured as a GUC parameter, while the other is hardcoded to 10. Best Regards, Fei Changhong
v1-0002-Optimize-commit-with-temp-tables-guc.patch
Description: Binary data
v1-0002-Optimize-commit-with-temp-tables-without-guc.patch
Description: Binary data