Horton

Re: Performance degradation of REFRESH MATERIALIZED VIEW

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>

View thread View on postgr.es
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Andres Freund <andres@anarazel.de>, 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-11T10:58:23Z
Lists: pgsql-hackers 
On Tue, May 11, 2021 at 2:34 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> 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 may not have understood what's being discussed here completely, but
if you want to use multi inserts for refresh matview code, maybe the
"New Table Access Methods for Multi and Single Inserts" patches at
[1], can help.

[1] - https://www.postgresql.org/message-id/CALj2ACXdrOmB6Na9amHWZHKvRT3Z0nwTRsCwoMT-npOBtmXLXg%40mail.gmail.com

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com

Commits

  1. Fix pg_visibility regression failure with CLOBBER_CACHE_ALWAYS

  2. Revert most of 39b66a91bd

  3. Fix COPY FREEZE with CLOBBER_CACHE_ALWAYS