Re: Performance degradation of REFRESH MATERIALIZED VIEW

On Sat, May 22, 2021 at 3:10 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> On 5/21/21 6:43 PM, Andres Freund wrote:
> > Hi,
> >
> > ...
> >
> >> Attached are the flame graphs for all three cases. The change in master is
> >> pretty clearly visible, but I don't see any clear difference between old and
> >> patched code :-(
> >
> > I'm pretty sure it's the additional WAL records?
> >
>
> Not sure. If I understand what you suggested elsewhere in the thread, it
> should be fine to modify heap_insert to pass the page recptr to
> visibilitymap_set, roughly per the attached patch.
>
> I'm not sure it's correct, but it does eliminate the Heap2/VISIBILITY
> records for me (when applied on top of your patch). Funnily enough it
> does make it a wee bit slower:
>
> patch #1: 56941.505
> patch #2: 58099.788
>
> I wonder if this might be due to -fno-omit-frame-pointer, though, as
> without it I get these timings:
>
> 0c7d3bb99: 25540.417
> master: 31868.236
> patch #1: 26566.199
> patch #2: 26487.943
>
> So without the frame pointers there's no slowdown, but there's no clear
> improvement after removal of the WAL records either :-(

Can we verify that the additional WAL records are the cause of this
difference by making the matview unlogged by manually updating
relpersistence = 'u'?

Here are the results of benchmarks with unlogged matviews on my environment:

1) head: 22.927 sec
2) head w/ Andres’s patch: 16.629 sec
3) before 39b66a91b commit: 15.377 sec
4) head w/o freezing tuples: 14.551 sec

And here are the results of logged matviews ICYMI:

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 seems no difference in the tendency. Which means the additional
WAL is not the culprit?

Interestingly, my previously proposed patch[1] was a better
performance. With the patch, we skip all VM-related work on all
insertions except for when inserting a tuple into a page for the first
time.

logged matviews: 31.591 sec
unlogged matviews: 15.317 sec

Regards,

[1] https://www.postgresql.org/message-id/CAD21AoAaiPcgGRyJ7vpg05%3DNWqr6Vhaay_SEXyZBboQcZC8sFA%40mail.gmail.com

Regards,

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