Hi, On 2021-03-11 17:44:37 +0900, Masahiko Sawada wrote: > The execution time of REFRESH MATERIALIZED VIEW are: > > w/ HEAP_INSERT_FROZEN flag : 42 sec > w/o HEAP_INSERT_FROZEN flag : 33 sec > > After investigation, I found that such performance degradation happens > on only HEAD code. It seems to me that commit 39b66a91b (and > 7db0cd2145) is relevant that has heap_insert() set VM bits and > PD_ALL_VISIBLE if HEAP_INSERT_FROZEN is specified (so CCing Tomas > Vondra and authors). Since heap_insert() sets PD_ALL_VISIBLE to the > page when inserting a tuple for the first time on the page (around > L2133 in heapam.c), every subsequent heap_insert() on the page reads > and pins a VM buffer (see RelationGetBufferForTuple()). Reading and > pinning a VM buffer for every insertion is a very high cost. This > doesn't happen in heap_multi_insert() since it sets VM buffer after > filling the heap page with tuples. Therefore, there is no such > performance degradation between COPY and COPY FREEZE if they use > heap_multi_insert() (i.g., CIM_MULTI). Paul also reported it in that > thread.
Probably worth adding as an open item for 14. Greetings, Andres Freund
