Re: Optimize commit performance with a large number of 'on commit delete rows' temp tables

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