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

Hi wenhui, I carefully analyzed the reason for the performance regression with fewer temporary tables in the previous patch (v1-0002-): the k_hash_funcs determined by the bloom_create function were 10(MAX_HASH_FUNCS), which led to an excessive calculation overhead for the bloom filter.

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

Hi feichanghong I think adding an intercept this way is better than implementing a global temp table,there is a path to implement a global temporary table ( https://www.postgresql.org/message-id/1a1a6edc-d0ec-47b0-bd21-c2acbaea6...@alibaba-inc.com),you can consult with them ,they work at

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

Hi wenhui, > On Jul 8, 2024, at 12:18, wenhui qiu wrote: > > Hi feichanghong > I don't think it's acceptable to introduce a patch to fix a problem that > leads to performance degradation, or can we take tom's suggestion to optimise > PreCommit_on_commit_actions? I think it to miss the

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

Hi feichanghong I don't think it's acceptable to introduce a patch to fix a problem that leads to performance degradation, or can we take tom's suggestion to optimise PreCommit_on_commit_actions? I think it to miss the forest for the trees Best Regards, feichanghong 于2024年7月8日周一 10:35写道：

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

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

Hi feichanghong Thanks for updating the patch ,I think could be configured as a GUC parameter,PostgreSQL has too many static variables that are written to death and explicitly stated in the code comments may later be designed as parameters. Now that more and more applications that previously

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

The patch in the attachment, compared to the previous one, adds a threshold for using the bloom filter. The current ON_COMMITS_FILTER_THRESHOLD is set to 64, which may not be the optimal value. Perhaps this threshold could be configured as a GUC parameter? Best Regards, Fei Changhong

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

Thank you for your attention and suggestions. > On Jul 6, 2024, at 00:15, Tom Lane wrote: > > writes: >> PostgreSQL maintains a list of temporary tables for 'on commit >> drop/delete rows' via an on_commits list in the session. Once a >> transaction accesses a temp table or namespace, the >>

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

"=?ISO-8859-1?B?ZmVpY2hhbmdob25n?=" writes: > PostgreSQL maintains a list of temporary tables for 'on commit > drop/delete rows' via an on_commits list in the session. Once a > transaction accesses a temp table or namespace, the > XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is set. Before committing,

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

Hi hackers, # Background PostgreSQL maintains a list of temporary tables for 'on commit drop/delete rows' via an on_commits list in the session. Once a transaction accesses a temp table or namespace, the XACT_FLAGS_ACCESSEDTEMPNAMESPACE flag is set. Before committing, the