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

feichanghong <feichanghong@qq.com>

From: feichanghong <feichanghong@qq.com>

To: wenhui qiu <qiuwenhuifx@gmail.com>

Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@lists.postgresql.org>

Date: 2024-07-08T04:41:43Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Lift limitation that PGPROC->links must be the first field
- 98347b5a3ab1 18.0 cited

Hi wenhui,

> On Jul 8, 2024, at 12:18, wenhui qiu <qiuwenhuifx@gmail.com> 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 forest for the trees

You're right, any performance regression is certainly unacceptable. That's why
we've introduced a threshold. The bloom filter optimization is only applied
when the number of temporary tables exceeds this threshold. Test data also
reveals that with a threshold of 10, barring cases where all temporary tables
are implicated in a transaction, there's hardly any performance loss.

"Improve PreCommit_on_commit_actions by having it just quit immediately if
XACT_FLAGS_ACCESSEDTEMPNAMESPACE is not set" can only reduce the overhead of
traversing the OnCommitItem List but still doesn't address the issue with
temporary table truncation.

Looking forward to more suggestions!

Best Regards,
Fei Changhong