pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joao Rito <joaovictor.rito@gmail.com>
To: Rafia Sabih <rafia.pghackers@gmail.com>
To: surya poondla <suryapoondla4@gmail.com>
Cc: Yugo Nagata <nagata@sraoss.co.jp>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: cca5507 <cca5507@qq.com>
Cc: Giuliano Gagliardi <gogi@gogi.tv>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date: Sat, 14 Nov 2026 18:33:56 -0300
Message-ID: <20261114213356.310-1-joaovictor.rito@gmail.com> (raw)
In-Reply-To: <20260731164448.f84babf2a951ad20e68058a8@sraoss.co.jp>
References: <20260731164448.f84babf2a951ad20e68058a8@sraoss.co.jp>

Hi Rafia, hi Surya,

Thank you for the patches. I tested the v7 series (v7-0001 for the
duplicate detection and v7-0002 for the nullable indexed columns). My
results are good for 0001 and mixed for 0002, details below.

What I did:

- Applied both patches to current master (0dbe240dc0). They apply cleanly
  and build without warnings (-O2, --without-icu, no --enable-cassert).
- make check passes (243 tests) on master, with 0001 and with 0001+0002,
  including the new matview regression tests.
- Correctness, comparing master, 0001 and 0001+0002 on a few small cases:
  - Two identical rows (test, NULL) with a unique index on the first
    column: on master REFRESH ... CONCURRENTLY succeeds without error and
    leaves 1 row in the view while the query returns 2. With 0001 (and
    with 0002) it raises "new data for materialized view "m" contains
    duplicate rows"; the plain REFRESH also fails on this data (with a
    unique index error), so the results now agree.
  - Same with a third row added, (k1, NULL, 1) twice plus (k2, z, 2): master
    silently keeps 2 rows, 0001 raises the error.
  - Two (NULL, NULL) rows with a unique index on a: succeeds in all three
    builds, 2 rows. This also matches Yugo's note that (NULL, NULL) twice
    already worked on master. Two-column unique index with duplicates only
    through NULL: succeeds in all three. A true duplicate (r,s,2) twice
    errors in all three, only the message text changes (the "without any
    null columns" part is gone). NULLS NOT DISTINCT gives the same unique
    violation in all three.
  - 200k rows with about 20 percent NULLs, two unique indexes, then
    updates and deletes and a refresh: 0 rows missing and 0 extra in all
    three builds (198012 rows).
- Performance on 2M rows (k is 50 percent NULL, unique indexes on (id) and
  on (k,id)) and on 1M rows (y all NULL, unique indexes on x and on y):
  - 0001 changes the plan of the duplicate pre-check from a merge semi
    join with two sorts (37.1 s) to a hash semi join (9.95 s), in one
    auto_explain run.
  - With 0002 I could not see the benefit. A refresh with no data change
    still deletes and inserts 3,000,000 tuples after 3 refreshes in all
    three builds, in both scenarios. The join of the diff query still has
    "mv.k = newdata.k" in the hash condition with 0002 (same plan as with
    0001 alone).
  - I think this agrees with Surya's note of 21 July that 0002 does not
    help. My guess for the reason: 0002 skips nullable columns only when
    attr->attnotnull is set for some index column, but the columns of a
    materialized view show attnotnull = f even when the source column is
    NOT NULL (I checked pg_attribute). If I read the patch right, the new
    branch is then never taken for a materialized view. I did not debug
    this further, so please take it as a guess. The new regression tests
    for 0002 pass, but they only check that the refresh succeeds, so they
    would not notice this.

Timings are noisy on my machine (one refresh of the 2M row case took from
24 s to 88 s), so I would not read anything into the elapsed times, only
into the tuple counts and the plans.

I did not test: --enable-cassert, TAP tests or check-world (only make
check), meson builds, the back branches, concurrent refreshes, expression
or partial unique indexes, row security, or materialized views bigger than
2M rows. Each timing is a single sequence of three runs.

Thanks again,

Joao Rito






view thread (26+ messages)  latest in thread

Message-ID: <20261114213356.310-1-joaovictor.rito@gmail.com>
Permalink:  ../20261114213356.310-1-joaovictor.rito@gmail.com/
Also on:    postgresql.org/message-id/20261114213356.310-1-joaovictor.rito@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: joaovictor.rito@gmail.com, rafia.pghackers@gmail.com, suryapoondla4@gmail.com, nagata@sraoss.co.jp, robertmhaas@gmail.com, cca5507@qq.com, gogi@gogi.tv
  Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
  In-Reply-To: <20261114213356.310-1-joaovictor.rito@gmail.com>
  References: <20260731164448.f84babf2a951ad20e68058a8@sraoss.co.jp> <20261114213356.310-1-joaovictor.rito@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox