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 1vsq5i-003eVk-17 for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Feb 2026 22:32:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vsq5h-000gS0-0s for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Feb 2026 22:32:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vsq5g-000gRs-2v for pgsql-bugs@lists.postgresql.org; Wed, 18 Feb 2026 22:32:17 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vsq5d-000000002oJ-2f94 for pgsql-bugs@lists.postgresql.org; Wed, 18 Feb 2026 22:32:16 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-678b85b972dso130914eaf.1 for ; Wed, 18 Feb 2026 14:32:14 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1771453933; cv=none; d=google.com; s=arc-20240605; b=i4bTwcbWP8Ok3AFv8CMnm9rJlAM3bItT9Me09TuLIgmj1IDZ5TxNchrwbYfMDfKdAm fwzcAux1zmljtqVDZe+BXA/pTUi8qM+O5AWuUUwPme4pd+KrZJLlEiKluuvvDFlxyJPd 6bDHT0ThjTdaPFXN43mKLkzhvfytFLyF0Xg+ezMPUQpAApwOVokD9r+R9ZmBHsCn/NhK yRa/BAJqxzwriS6LUBBXoZnV21acvmTOCIaMtWvf6Ol8xGRkNSRR5RZlmNNi+d0874Na 6q8hL2m95Rafkuz6OeGjEFBt5JzKikNB7FG9qUbwWl74wEqgox7pIcRKW2Zq/XOC53e/ qKIg== 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=AERpJKoi9lHfpzuFomc4PxwxfpPf/41Roiuoij7ryj0=; fh=NighQexWNpTbJvk3dMdt5zrdNQGOvvaJSBE0FI6G+p4=; b=ZsU2p37/JYd05/1PH5uqVpRPFU54jdl219xD4dJcVSVJZmBUyJDQIv96NWEkTZIfXo BhQvv3uXNElNxn+2CadZc1Ob/VEzRH5pAYNi9P3eZdUPTYln708xk1zWacckz30Tyhyk rhtL0xBIMxowZ+o046LgOB8vMyR9+4GGt4xM2xhUy/KrdtlhzJwFh5DcX2ASxCWq+XS4 u7VwAgntUOwmXo9h669rNzfiK8MqGn4W8AyKqfVl/n/QPhj8+PnJBROSU1Y54/Op5/eE JYeVqiNTLLZsjVwazPGLYoiGNtWM2VfZDv2ED8qfn+Xp82vjRADfAkgzGsV7OpBxjNYd CHPQ==; 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=1771453933; x=1772058733; 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=AERpJKoi9lHfpzuFomc4PxwxfpPf/41Roiuoij7ryj0=; b=LQ4sMstc2tiRqdyibMqvmRPaF2x29y2TvAuG/zqadeSvBgQ4WfDkPDfzwMw3J9+9Me l5RN76SMiybSPG2EA88MzmLjteadAuhP/k49jOdjcFSK9+z/BWmL7MbHWARuXGpvamqA /LUhMscMCipiQwHi/U4ILtd9a7ETNgqjrM7AvgV/cjmxPmXXAvF71Mx7/BvXgkaZ/7w0 KwaTwPlTsnfmKJlILVkqgbkDyK1+87tRKfigqjLosS1sOS9MbPUBypEUschFU3Vqoauq 7FpASy0ir8ZpOBNPCLFTfyCibOOarx4RQ6w1dPusvWZJFcys/MdvzZt2zvjcfJ0iNHHI KBFA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771453933; x=1772058733; 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=AERpJKoi9lHfpzuFomc4PxwxfpPf/41Roiuoij7ryj0=; b=SgPq2sSUyr88UF7a7ZDyKAgxOj9/j6+PgCSctwq2Y10YdxZUKfhmO1E3Im/pUhrA7n oopRASlmddnTijl4tbucAj80yWZrowYxy3jaj5x4bFuVwjITSSsClrdWo42tmuTholmd 2pE5kcypYyFTlAI3h3ziIS72eSdd+M8taxGpx2vIS8TyN2nUkUdObOjBHUgWpr6MkLEn IYSbXa7q5VCUPE3A/YPkN+aeitXij+jAuiY62nt8QXC4tiQ2o/48SNSZQt8hkEA3L94M sCR8v/XujqAu4XsAH/nh8gBzdxD7z5BqljyWet5lWloMtWVpq69Qi6iI7dJM/t/U5Wqp OKaw== X-Forwarded-Encrypted: i=1; AJvYcCUj88riCpdtIZeSyUbNEKB8+4EhiHinP2zMwfR0A5Zw4OVfWejNh331m+GFOcSBYGLHnmVSS0apwSPD@lists.postgresql.org X-Gm-Message-State: AOJu0YwKAawZMYJ1G9K459F6ea/U3FAdEDGqv8af8IexOd/5di6YTiLd eIl2PGraZAZPFVJ06TKT6DmuE4/8Ncl0amH4exNM2mEchE4dOKf5qAhmYlGBoM0BTditNztmt1M W7sZBMwwDMrA1OynIXJ943NC45KN1EvjVmZ9nK8c= X-Gm-Gg: AZuq6aK9+Y+aaKaTYR8HU6HjqqdZdigmndPJGhYm6/sv+KXqBe1DHd01cTgcljD/jx9 qRgTSW+Te8RuE8QIGhd6FmHpnShUFcVDOPaYC8NRlMk5Zi9YBT5NmN55yte6oTfvvoVbvsryk5i +mfc8o6cFYvQ8yeeT9OnvhM57OfHcL8mkBjZuJ06PMmbENkZAGm9eyCuvN1ngBG5sgzVBZ98VuO k9JSf+91YuulXBROXHVZ9vci7W3qe0lgpD96IZxTiQMJ8/0QE0TcdyJSRrmgn6k1xMwmL/egGbs I1T29cb0AVILfw== X-Received: by 2002:a05:6820:f023:b0:672:9d81:a765 with SMTP id 006d021491bc7-6785bb672bfmr8266922eaf.65.1771453932706; Wed, 18 Feb 2026 14:32:12 -0800 (PST) MIME-Version: 1.0 References: <40d694df-39fd-4a4a-9459-9d6489165f60@gogi.tv> In-Reply-To: From: surya poondla Date: Wed, 18 Feb 2026 14:32:01 -0800 X-Gm-Features: AaiRm53T-p0aENX7KK2Pcbj_llqILOHpWrzj15b-1JfryzLsA14orJIxys7YJUI Message-ID: Subject: Re: Two issues with REFRESH MATERIALIZED VIEW CONCURRENTLY To: Rafia Sabih Cc: Giuliano Gagliardi , pgsql-bugs@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000652010064b20c2e4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000652010064b20c2e4 Content-Type: text/plain; charset="UTF-8" Hi All, Also, for issue 1, additional test case should be added. > Sure, I will add test cases for issue 1. 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. > I ran check, check-world and didn't see any regressions. 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 >> > Regrading the performance, (quoting the output from my previous message) with unique index having NULL values we see that both "REFRESH MATERIALIZED VIEW CONCURRENTLY s;" operations (operation 1 was after CREATE UNIQUE INDEX ON s(x); and operation 2 was after CREATE UNIQUE INDEX ON s(x);) take about the same time. Without the patch, operation 2 was taking around ~11000 ms, due to NULL = NULL comparison checks and this was causing the degradation. Regarding different commits to each issue, I don't have any particular opinion but since both the issues are related to the same function and NULL comparison, I feel we can have a single commit, but open to create 2 commits too. Regards, Surya Poondla --000000000000652010064b20c2e4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi All,


Also, for issue 1, additional test case= should be added.
Sure, I will add test cases = for issue 1.
=C2=A0

For issue 2, it would be hel= pful if you may share some performance=C2=A0numbers to confirm if this solu= tion is only improving the performance and not causing any regressions.
I ran check, check-world and didn't see any r= egressions.

Her= e is the output 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
Regrading the performance, (quoting the out= put from my previous message) with unique index having NULL values we see t= hat both "REFRESH MATERIALIZED VIEW CONCURRENTLY s;" operations (= operation 1 was after CREATE UNIQUE INDEX ON s(x); and operation 2 was afte= r CREATE UNIQUE INDEX ON s(x);) take about the same time. Without the patch= , operation 2 was taking around ~11000 ms,=C2=A0due to NULL =3D NULL compar= ison=C2=A0checks and this was causing the degradation.
=C2=A0
Regarding different commits to each issue, I don't ha= ve any particular=C2=A0opinion but since both the issues are related to the= same function and NULL comparison, I feel we can have a single commit, but= open to create 2 commits too.

Regards,
Surya Poondla

=C2=A0
--000000000000652010064b20c2e4--