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