Re: Performance degradation of REFRESH MATERIALIZED VIEW

On Tue, May 11, 2021 at 4:37 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Wed, May 05, 2021 at 03:04:53PM +0200, Tomas Vondra wrote:
> > Thanks, that looks promising. I repeated the tests I did on 26/4, and the
> > results look like this:
> >
> > old (0c7d3bb99): 497ms
> > master: 621ms
> > patched: 531ms
> >
> > So yeah, that's a bit improvement - it does not remove the regression
> > entirely, but +5% is much better than +25%.
>
> Hmm. Is that really something we should do after feature freeze? A
> 25% degradation for matview refresh may be a problem for a lot of
> users and could be an upgrade stopper. Another thing we could do is
> also to revert 7db0cd2 and 39b66a9 from the v14 tree, and work on a
> proper solution for this performance problem for matviews for 15~.

I think the approach proposed by Andres eliminates the extra vmbuffer
reads as much as possible. But even with the patch, there still is 5%
degradation (and there is no way to disable inserting frozen tuples at
matview refresh). Which could be a problem for some users. I think
it’s hard to completely eliminate the overhead so we might need to
consider another approach like having matview refresh use
heap_multi_insert() instead of heap_insert().

I think the changes for heap_multi_insert() are fine so we can revert
only heap_insert() part if we revert something from the v14 tree,
although we will end up not inserting frozen tuples into toast tables.

Regards,

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