"=?ISO-8859-1?B?ZmVpY2hhbmdob25n?=" <feichangh...@qq.com> 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, the > PreCommit_on_commit_actions function truncates all 'commit delete > rows' temp tables, even those not accessed in the current transaction. > Commit performance can degrade if there are many such temp tables.
Hmm. I can sympathize with wanting to improve the performance of this edge case, but it is an edge case: you are the first to complain about it. You cannot trash the performance of more typical cases in order to get there ... > In the attached patch (based on HEAD): > - A Bloom filter (can also be a list or hash table) maintains > the temp tables accessed by the current transaction. ... and I'm afraid this proposal may do exactly that. Our bloom filters are pretty heavyweight objects, so making one in situations where it buys nothing is likely to add a decent amount of overhead. (I've not tried to quantify that for this particular patch.) I wonder if we could instead add marker fields to the OnCommitItem structs indicating whether their rels were touched in the current transaction, and use those to decide whether we need to truncate. Another possibility is to make the bloom filter only when the number of OnCommitItems exceeds some threshold (compare d365ae705). BTW, I wonder if we could improve PreCommit_on_commit_actions by having it just quit immediately if XACT_FLAGS_ACCESSEDTEMPNAMESPACE is not set. I think that must be set if any ON COMMIT DROP tables have been made, so there should be nothing to do if not. In normal cases that's not going to buy much because the OnCommitItems list is short, but in your scenario maybe it could win. regards, tom lane