Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vr2j7-005R74-2w for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Feb 2026 23:37:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vr2j6-00GeI4-2V for pgsql-bugs@arkaria.postgresql.org; Fri, 13 Feb 2026 23:37:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vr2j6-00GeHv-0d for pgsql-bugs@lists.postgresql.org; Fri, 13 Feb 2026 23:37:32 +0000 Received: from mail-lf1-x129.google.com ([2a00:1450:4864:20::129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vr2j3-00000000WRg-2Jlm for pgsql-bugs@lists.postgresql.org; Fri, 13 Feb 2026 23:37:31 +0000 Received: by mail-lf1-x129.google.com with SMTP id 2adb3069b0e04-59de0b7c28aso1578508e87.1 for ; Fri, 13 Feb 2026 15:37:30 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1771025847; cv=none; d=google.com; s=arc-20240605; b=ATeNqY33cOayoz38DEVEVEpyfEN4deEpbsMrMc2iVqVwU6u4wLAF/pEgALdAFHTe9C A5bzQJxdeQNPWxCsOJZBOvPkQPdI+PbTGmPfS5nmP8IfGJP/kZdS00xYAkF+PAS6rzbY VoQXniw/Y3GIRySj/ZvkLM3H1j8V+IaZXAyidtaAoqPhkx7mPcjiEZYE0c5AGnpNebpI SnQ5CL/q6ibstyJuveMKkSHGTk7/wiMXtmes7a2pM7tT7NWMpu7qDAPvbOyGQJ+LC/dC WkNq+tJg9Pex5Vw2GAtnaphM/mKuM84fy2DHYVTWyzY0kqifk8S2RMkrhAkUTFvn7IRE 8S7Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=7riI0t7MSSo9owyzZB8ROCEP98R0lp/LMjAIN8aRCyU=; fh=e5o2eUj0hNiWJ9i9hJzpk1Wv8lSUGBQcRH9hAbPcnC0=; b=Fq03Z0mBuH4OuIG+2sO8MC1NO6XWvVXZiqZOu5sKC8BSSDYmTIEZPI3i/t10O6yg7/ saY6Dd2XOIsNbj273Qh76QkJYlK+VdHIt/vdcx7isCqwa6ALtRRmxn5mVnWcnf2zn9Fn CgX1AjWLh7EXXa8yagow2bPJM3p1Id7ZvsPz7nMR1RAUzssV/9uS2NJUvvbcIdFH7Fui fIVczAlYZ7Y6D2K0MZ9fv5Ekxa1ACHSigYVdXsqiVqyuTYeQCgkB/zHex5Y8TQrAY5p4 g9y3ODGk4unG2MTsAHp8budI7BjEsjKaLc/PtBh3gvGlweA0pjDbw5frsIjv3ahgQ1kv E+8A==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1771025847; x=1771630647; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=7riI0t7MSSo9owyzZB8ROCEP98R0lp/LMjAIN8aRCyU=; b=l9y22kHGp6hDyVPHPcChoW1Z8kLvKK9UemTgBpbjkkHz9o2fMhA52NKEW/e9izKs0Q M49/nw2L83yjh43N0S/K0+ETw5NqDe6NSnnIpDFSpT4RFOEKqN9RGPuBJFf9ME/O8Rx+ p5LkeMiRB6+LBl8C0n3OIEgfLheBU5+U8f2Fo1LZcXPOP1miutpNePzc6uGwRTc3HM8h JqRouxzxfEjHtX9Xf07Bm7SwN3h1qL3jSdznoUxSlv9W4VbHeQVZqBJp+dWoW4+ZgWnI 0gwpL0hZ7httELdGN17IFLGqi9IOaP374OOrjkaI4rnnUfeclkue64bbzK56WTq9Ebt5 N1FQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771025847; x=1771630647; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=7riI0t7MSSo9owyzZB8ROCEP98R0lp/LMjAIN8aRCyU=; b=E+zmTwz2FxoK5IphhcFgJ3KFM3UZCvkPzcjyAHRak1olA+hNaX/1QsKva+/VkZ/vq6 AP752RSEeH439kUb3LaUygpXZ2A/Mm5X3U40G4Pt5ioXh+kxQTYate5zf8K0HAQxTsvf yfg6Gf/xe4f4L1dMHHMO902x6onnjVuWFG71wXjpzwXlmGYfjjB0jjK7Vb2RwpAsEvbG TEYhBd7zIIFmfVbbJDV+D2qXT7LbVdV5jwqXamENTEYEjyERR7jcQz8qqH+ZDy8GYEuQ zUWiv6knzm33LrcXyzvI9kXfsZNjXXUiwOMve6F3xR3Fo+qvalFVoxkaqhmiuOBWfPpn RSMQ== X-Forwarded-Encrypted: i=1; AJvYcCXVsJK6V+4bXzkDPrvtWPJ0PjT1llb25OakBtr9dOxvZdHcQPUleSJpthegozZJzbc131EcgidW5BD6@lists.postgresql.org X-Gm-Message-State: AOJu0YxBy0ZF3gSTDyEpFgMi9mYcnD0mF9j+rl46Ca0mHIebLhswDNMq z0NiamvX8LOFuvehlE2LBL2KbAQXnfENMlu9dpfieb+J3+HcS1dC6HNdVGmmVZ2PmNTyY8gmpr8 I81tNgwJUqIJrCQPQ5Q0Lv2/65wrNKg4= X-Gm-Gg: AZuq6aJxsmqYR16MGtkWYnoxDx94MW73Q5UOgQTszQH3SmJeh590ILVuFnLs/Vy+fpx N322M3bAghTw51aEnKbycv/goEUzvvRbfDP+M5oJ1P7sml8i+OXfRwO/E6+WgzakPZtZGWVLHtf R//Vcsj+wsRgVD1tG/ZIeIDqI/A59t+pzzzTXFsPk/7HWXZSC2ZVlW4cP5hvlDiY32hdQFdkX3q DGY1PWM7P3V7/tN62KRQu+CPs/iCMYkF/5e0L6P6oOAhcknd6SXam1Q8cSlt3XIknvd0uG89TQi gnOH16xHTaw+k1l9VASFE2j3yTL3uFbne+kQ6KCoDwYbZgTVanU0/j+hyqPMeHs7tnaSqz1gkeJ WBabqVsLVGI7hLNg= X-Received: by 2002:a05:6512:39ce:b0:59e:657f:438d with SMTP id 2adb3069b0e04-59f69c64d85mr1405634e87.34.1771025846718; Fri, 13 Feb 2026 15:37:26 -0800 (PST) MIME-Version: 1.0 References: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv> In-Reply-To: From: Rafia Sabih Date: Sat, 14 Feb 2026 00:37:15 +0100 X-Gm-Features: AZwV_QiXf59E1DZirz-KeQJYKzhGuNjzAxgaoopjsWuqfhdK18BftXXbVBUBoyQ Message-ID: Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY To: surya poondla Cc: Giuliano Gagliardi , pgsql-bugs@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000007b54c2064abd167d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007b54c2064abd167d Content-Type: text/plain; charset="UTF-8" On Thu, 12 Feb 2026 at 18:08, surya poondla wrote: > Hi All, > > Right now, I am exploring the issue 2. >> > > I am not sure if anyone else has submitted a patch for issue 2. I don't > see any updates from the mailing list yet. I guess the mailing list has > some delays, apologies if my efforts look duplicated to other people's > efforts. > > I have a potential patch for issue 2. > I was able to reproduce the issue, saw the performance degradation, and, > with my patch (attached) I see an improvement in the REFRESH MATERIALIZED > VIEW CONCURRENTLY. > > The main crux of issue 2 is: when a materialized view has unique index on > a nullable column, and when we did REFRESH MATERIALIZED VIEW CONCURRENTLY > it would include that column in the FULL OUTER > JOIN condition used to detect changes. > The nullable column was showing severe performance degradation because > NULL = NULL comparisons evaluate to NULL, making all rows appear different > even when unchanged! > > The fix I explored is to skip nullable columns when building the FULL > OUTER JOIN conditions. Only include columns with NOT NULL constraints from > unique indexes. The record equality operator (*=) is always included and > handles nullable columns correctly. > > Here is the output and performance improvement: > > postgres=# \timing on > > Timing is on. > > postgres=# DROP MATERIALIZED VIEW IF EXISTS s CASCADE; > > NOTICE: materialized view "s" does not exist, skipping > > DROP MATERIALIZED VIEW > > Time: 0.858 ms > > postgres=# > > postgres=# CREATE MATERIALIZED VIEW s AS SELECT generate_series as x, null > as y FROM generate_series(1, 1000000); > > SELECT 1000000 > > Time: 1076.254 ms (00:01.076) > > postgres=# > > postgres=# CREATE UNIQUE INDEX ON s(x); > > CREATE INDEX > > Time: 375.026 ms > > postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY s; > > REFRESH MATERIALIZED VIEW > > Time: 3807.143 ms (00:03.807) > > postgres=# CREATE UNIQUE INDEX ON s(y); > > CREATE INDEX > > Time: 331.382 ms > > postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY s; > > REFRESH MATERIALIZED VIEW > > Time: 3636.049 ms (00:03.636) > postgres=# > > As we can see the REFRESH MATERIALIZED VIEW CONCURRENTLY now takes 3636.049 > ms > > With the current patch for issue 2, there is a trade-off. > The fix skips nullable columns from the join condition to avoid slowness > when NULLs exist (9s vs 3s in testing). This may slightly slow down cases > where nullable columns (unique index) never contain NULLs. > Users can restore full performance by adding the NOT NULL constraints to > the column if they know there will never be any nulls on that column. > > I would love to hear any feedback on this tradeoff and am happy to > implement relevant changes. > > Note: The attached patch addresses both issue 1, issue 2. > > Regards, > Surya Poondla. > > Thanks for working on this. Firstly, since both are different issues, it makes sense to write patches for each of them separately. Secondly, for issue 1 it is important to understand why the code was explicitly done for null columns, what are the scenarios in which this modified code could cause issues. Also, for issue 1, additional test case should be added. For issue 2, it would be helpful if you may share some performance numbers to confirm if this solution is only improving the performance and not causing any regressions. -- Regards, Rafia Sabih CYBERTEC PostgreSQL International GmbH --0000000000007b54c2064abd167d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, 12 Feb = 2026 at 18:08, surya poondla <suryapoondla4@gmail.com> wrote:
Hi All,

Right now, I am= exploring the issue 2.

=
I am not sure if anyone else has submitted a patch for issue 2. I don&= #39;t see any updates from the mailing list yet. I guess the mailing list h= as some delays, apologies if my efforts look duplicated to=C2=A0other=C2=A0= people's efforts.=C2=A0

I have a potential patch for issue 2.I was able to reproduce the issue, saw the performance=C2=A0degradation, a= nd, with my patch (attached) I see an improvement=C2=A0in the=C2=A0REFRESH = MATERIALIZED VIEW CONCURRENTLY.

The main crux of issue 2 is: when a = materialized view has unique index on a nullable column, and when we did RE= FRESH MATERIALIZED VIEW CONCURRENTLY it would include that column in the FU= LL OUTER
JOIN condition used to detect changes.
The nullable column = was showing severe performance degradation because NULL =3D NULL comparison= s evaluate to NULL, making all rows appear different even when unchanged!

The fix I explored is to skip nullable columns when= building the FULL OUTER JOIN conditions. Only include columns with NOT NUL= L constraints from unique indexes. The record equality operator (*=3D) is a= lways included and handles nullable columns correctly.

Here is the o= utput and performance improvement:

postgres=3D# \timing on<= /p>

Timing is on.

postgres=3D# DROP MATERI= ALIZED VIEW IF EXISTS s CASCADE;

NOTICE:=C2=A0 materialized view "s" does not exist, skipping

DROP MATERIALIZED VIEW

Time: 0.858 ms

postgres=3D#

postgres=3D# CREATE MATE= RIALIZED VIEW s AS SELECT generate_series as x, null as y FROM generate_ser= ies(1, 1000000);

SELECT 1000000

Time: 1076.254 ms (00:01= .076)

postgres=3D#

postgres=3D# CREATE UNIQ= UE INDEX ON s(x);

CREATE INDEX

Time: 375.026 ms

postgres=3D# REFRESH MAT= ERIALIZED VIEW CONCURRENTLY s;

REFRESH MATERIALIZED VIE= W

Time: 3807.143 ms (00:03= .807)

postgres=3D# CREATE UNIQ= UE INDEX ON s(y);

CREATE INDEX

Time: 331.382 ms

postgres=3D# REFRESH MAT= ERIALIZED VIEW CONCURRENTLY s;

REFRESH MATERIALIZED VIE= W

Time: 3636.049 ms (00:03= .636)

postg= res=3D#

As we can see the=C2=A0REFRESH MATERIALIZED VIEW=C2=A0CO= NCURRENTLY now takes=C2=A03636.049 ms

With the current patc= h for issue 2, there is a trade-off.
The fix skips nullable columns from= the join condition to avoid slowness when NULLs exist (9s vs 3s in testing= ). This may slightly slow down cases where nullable columns (unique index) = never contain NULLs.
Users can restore full performance by adding the NO= T NULL constraints to the column if they know there will never be any nulls= on that column.

I would love to hear any feedback on this tradeoff = and am happy to implement relevant changes.

Note: The attache= d patch addresses both issue 1, issue 2.

Regards,
Sury= a Poondla.

Thanks for working on this.=C2=A0
Firstly= , since both are different issues, it makes sense to write patches for each= of them separately.
Secondly, for issue 1 it is important to und= erstand why the code was explicitly done for null columns, what are the sce= narios in which this modified code could cause issues.
Also, for = issue 1, additional test case should be added.

For= issue 2, it would be helpful if you may share some performance=C2=A0number= s to confirm if this solution is only improving the performance and not cau= sing any regressions.
--
Regards,Rafia Sabih
CYBERTEC PostgreSQL International GmbH
--0000000000007b54c2064abd167d--