On Wed, May 19, 2021 at 3:08 AM Andres Freund <and...@anarazel.de> wrote: > > 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've ran the same benchmarks with the following parameters: shared_buffers = 10GB max_wal_size = 50GB min_wal_size = 50GB checkpoint_timeout = 1h maintenance_work_mem = 1GB work_mem = 512MB autovacuum = off 1) head: 42.397 sec 2) head w/ Andres’s patch: 34.857 sec 3) before 39b66a91b commit: 32.556 sec 4) head w/o freezing tuples: 32.752 sec There is 6% degradation between 2 and 4 but 2 is much better than the previous tests. > > > > 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? Sorry, I meant comparing 2 to 3 and 4. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
