Performance degradation of REFRESH MATERIALIZED VIEW

Hi,

While discussing freezing tuples during CTAS[1], we found that
heap_insert() with HEAP_INSERT_FROZEN brings performance degradation.
For instance, with Paul's patch that sets HEAP_INSERT_FROZEN to CTAS,
it took 12 sec whereas the code without the patch took 10 sec with the
following query:

create table t1 (a, b, c, d) as select i,i,i,i from
generate_series(1,20000000) i;

I've done a simple benchmark of REFRESH MATERIALIZED VIEW with the
following queries:

create table source as select generate_series(1, 50000000);
create materialized view mv as select * from source;
refresh materialized view mv;

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.

As far as I read the thread and commit messages related to those
commits, they are intended to COPY FREEZE and I could not find any
discussion and mention about REFRESH MATERIALIZED VIEW. So I'm
concerned we didn't expect such performance degradation.

Setting VM bits and PD_ALL_VISIBLE at REFRESH MATERIALIZED VIEW would
be a good choice in some cases. Since materialized views are read-only
VM bits never be cleared after creation. So it might make sense for
users to pay a cost to set them at refresh (note that CREATE
MATERIALIZED VIEW doesn’t set VM bits since it’s internally treated as
CTAS). On the other hand, given this big performance degradation
(about 20%) users might want to rely on autovacuum so that VM bits are
set in the background. However, unlike COPY, there is no way to
disable freezing tuples for REFRESH MATERIALIZED VIEW. So every user
would be imposed on those costs and affected by that performance
degradation. I’m concerned that it could be a problem.

What do you think?

Regards,

[1] https://www.postgresql.org/message-id/flat/FB1F5E2D-CBD1-4645-B74C-E0A1BFAE4AC8%40vmware.com

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/