Re: Performance degradation of REFRESH MATERIALIZED VIEW

Hi,

On 2021-05-18 11:20:07 +0900, Masahiko Sawada wrote:
> Yes. It depends on how much the matview refresh gets slower but I
> think the problem here is that users always are forced to pay the cost
> for freezing tuple during refreshing the matview. There is no way to
> disable it unlike FREEZE option of COPY command.
>
> I’ve done benchmarks for matview refresh on my machine (FreeBSD 12.1,
> AMD Ryzen 5 PRO 3400GE, 24GB RAM) with four codes: HEAD, HEAD +
> Andres’s patch, one before 39b66a91b, and HEAD without
> TABLE_INSERT_FROZEN.
>
> The workload is to refresh the matview that simply selects 50M tuples
> (about 1.7 GB). Here are the average execution times of three trials
> for each code:
>
> 1) head: 42.263 sec
> 2) head w/ Andres’s patch: 40.194 sec
> 3) before 39b66a91b commit: 38.143 sec
> 4) head w/o freezing tuples: 32.413 sec

I don't see such a big difference between andres-freeze/non-freeze. Is
there any chance there's some noise in there? I found that I need to
disable autovacuum and ensure that there's a checkpoint just before the
REFRESH to get halfway meaningful numbers, as well as a min/max_wal_size
ensuring that only recycled WAL is used.

> I also observed 5% degradation by comparing 1 and 2 but am not sure
> where the overhead came from. I agree with Andres’s proposal. It’s a
> straightforward approach.

What degradation are you referencing here?

I compared your case 2 with 4 - as far as I can see the remaining
performance difference is from the the difference in WAL records
emitted:

freeze-andres:

Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG/CHECKPOINT_ONLINE 1 ( 0.00) 114 ( 0.00) 0 ( 0.00) 114 ( 0.00)
Transaction/COMMIT 1 ( 0.00) 949 ( 0.00) 0 ( 0.00) 949 ( 0.00)
Storage/CREATE 1 ( 0.00) 42 ( 0.00) 0 ( 0.00) 42 ( 0.00)
Standby/LOCK 3 ( 0.00) 138 ( 0.00) 0 ( 0.00) 138 ( 0.00)
Standby/RUNNING_XACTS 2 ( 0.00) 104 ( 0.00) 0 ( 0.00) 104 ( 0.00)
Heap2/VISIBLE 44248 ( 0.44) 2610642 ( 0.44) 16384 ( 14.44) 2627026 ( 0.44)
Heap2/MULTI_INSERT 5 ( 0.00) 1125 ( 0.00) 6696 ( 5.90) 7821 ( 0.00)
Heap/INSERT 9955755 ( 99.12) 587389836 ( 99.12) 5128 ( 4.52) 587394964 ( 99.10)
Heap/DELETE 13 ( 0.00) 702 ( 0.00) 0 ( 0.00) 702 ( 0.00)
Heap/UPDATE 2 ( 0.00) 202 ( 0.00) 0 ( 0.00) 202 ( 0.00)
Heap/HOT_UPDATE 1 ( 0.00) 65 ( 0.00) 4372 ( 3.85) 4437 ( 0.00)
Heap/INSERT+INIT 44248 ( 0.44) 2610632 ( 0.44) 0 ( 0.00) 2610632 ( 0.44)
Btree/INSERT_LEAF 33 ( 0.00) 2030 ( 0.00) 80864 ( 71.28) 82894 ( 0.01)
-------- -------- -------- --------
Total 10044313 592616581 [99.98%] 113444 [0.02%] 592730025 [100%]

nofreeze:

Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG/NEXTOID 1 ( 0.00) 30 ( 0.00) 0 ( 0.00) 30 ( 0.00)
Transaction/COMMIT 1 ( 0.00) 949 ( 0.00) 0 ( 0.00) 949 ( 0.00)
Storage/CREATE 1 ( 0.00) 42 ( 0.00) 0 ( 0.00) 42 ( 0.00)
Standby/LOCK 3 ( 0.00) 138 ( 0.00) 0 ( 0.00) 138 ( 0.00)
Standby/RUNNING_XACTS 1 ( 0.00) 54 ( 0.00) 0 ( 0.00) 54 ( 0.00)
Heap2/MULTI_INSERT 5 ( 0.00) 1125 ( 0.00) 7968 ( 7.32) 9093 ( 0.00)
Heap/INSERT 9955755 ( 99.56) 587389836 ( 99.56) 5504 ( 5.06) 587395340 ( 99.54)
Heap/DELETE 13 ( 0.00) 702 ( 0.00) 0 ( 0.00) 702 ( 0.00)
Heap/UPDATE 2 ( 0.00) 202 ( 0.00) 0 ( 0.00) 202 ( 0.00)
Heap/HOT_UPDATE 1 ( 0.00) 65 ( 0.00) 5076 ( 4.67) 5141 ( 0.00)
Heap/INSERT+INIT 44248 ( 0.44) 2610632 ( 0.44) 0 ( 0.00) 2610632 ( 0.44)
Btree/INSERT_LEAF 32 ( 0.00) 1985 ( 0.00) 73476 ( 67.54) 75461 ( 0.01)
Btree/INSERT_UPPER 1 ( 0.00) 61 ( 0.00) 1172 ( 1.08) 1233 ( 0.00)
Btree/SPLIT_L 1 ( 0.00) 1549 ( 0.00) 7480 ( 6.88) 9029 ( 0.00)
Btree/DELETE 1 ( 0.00) 59 ( 0.00) 8108 ( 7.45) 8167 ( 0.00)
Btree/REUSE_PAGE 1 ( 0.00) 50 ( 0.00) 0 ( 0.00) 50 ( 0.00)
-------- -------- -------- --------
Total 10000067 590007479 [99.98%] 108784 [0.02%] 590116263 [100%]

I.e. the additional Heap2/VISIBLE records show up.

It's not particularly surprising that emitting an additional WAL record
for every page isn't free. It's particularly grating / unnecessary
because this is the REGBUF_WILL_INIT path - it's completely unnecessary
to emit a separate record.

I dimly remember that we explicitly discussed that we do *not* want to
emit WAL records here?

Greetings,

Andres Freund