Horton

Re: Performance degradation of REFRESH MATERIALIZED VIEW

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Andres Freund <andres@anarazel.de>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Michael Paquier <michael@paquier.xyz>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Pavan Deolasee <pavan.deolasee@gmail.com>, Anastasia Lubennikova <a.lubennikova@postgrespro.ru>, Jeff Janes <jeff.janes@gmail.com>, Paul Guo <guopa@vmware.com>
Date: 2021-05-21T18:10:13Z
Lists: pgsql-hackers

Attachments

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 :-(


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Commits

  1. Fix pg_visibility regression failure with CLOBBER_CACHE_ALWAYS

  2. Revert most of 39b66a91bd

  3. Fix COPY FREEZE with CLOBBER_CACHE_ALWAYS