public inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Giuliano Gagliardi <gogi@gogi.tv>
To: pgsql-bugs@lists.postgresql.org
Subject: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
Date: Mon, 9 Feb 2026 07:49:18 +0100
Message-ID: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv> (raw)

I noticed the following two issues while looking at the code that handles
REFRESH MATERIALIZED VIEW CONCURRENTLY (refresh_by_match_merge() in matview.c):

1.

At the beginning of the function, there is some code that checks for duplicate
rows, but it does not catch the following case:

CREATE TABLE t(a text, b text);
INSERT INTO t VALUES('test', null);
CREATE MATERIALIZED VIEW m AS SELECT * FROM t;
CREATE UNIQUE INDEX ON m(a);
INSERT INTO t VALUES('test', null); -- t now contains two identical rows

REFRESH MATERIALIZED VIEW CONCURRENTLY m;
     -> no error, but m still contains only one row!
REFRESH MATERIALIZED VIEW m;
     -> error (as expected)

2.

Do I understand correctly that the join creating the "diff" table is given
equality conditions for all columns referenced in any unique indexes? This
led me to think that a unique index on a column with many null entries
would enlarge the "diff" table.

In the following example, creating the second unique index noticeably worsens
the performance of REFRESH MATERIALIZED VIEW CONCURRENTLY:

CREATE MATERIALIZED VIEW s AS SELECT generate_series as x, null as y FROM generate_series(1, 1000000);
CREATE UNIQUE INDEX ON s(x);
REFRESH MATERIALIZED VIEW CONCURRENTLY s;
     -> runs for ~1700 ms
CREATE UNIQUE INDEX ON s(y);
REFRESH MATERIALIZED VIEW CONCURRENTLY s;
     -> runs for ~9000 ms

Kind regards,
Giuliano

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 using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: gogi@gogi.tv, pgsql-bugs@lists.postgresql.org
  Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY
  In-Reply-To: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv>

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

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