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-C2ACBAEA65E4@alibaba-inc.com),you
can consult with them ,they work at Alibaba

Best Regards,

feichanghong <feichanghong(at)qq(dot)com> 于2024年7月8日周一 12:42写道：

> Hi wenhui,
>
> On Jul 8, 2024, at 12:18, wenhui qiu <qiuwenhuifx(at)gmail(dot)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
>
>