Subject: Avoid calling SetMatViewPopulatedState if possible

From: "cca5507" <[email-masked]>
Date: Fri, 10 Apr 2026 11:36:43 +0800

Hi,

During refresh matview, we call SetMatViewPopulatedState unconditionally even
if the value doesn't change. Attach a small patch to optimize it.

--
Regards,
ChangAo Chen

---

From: David Geier <[email-masked]>
Date: Mon, 4 May 2026 20:29:47 +0200

Hi,

> if the value doesn't change. Attach a small patch to optimize it.

Thanks for sharing this patch.

While being a simple patch, it would be good to know what actual use
cases this change improves on and by how much. Can you share a test case
and/or performance data?

--
David Geier

---

From: "cca5507" <[email-masked]>
Date: Tue, 5 May 2026 19:58:34 +0800

Hi,

Thanks for your reply!

> cases this change improves on and by how much. Can you share a test case

The improvement of performance is small, so it's hard to observe it. But I think
the patch is still useful because we can avoid generating dead pg_class tuple:

create table t(a int);
create materialized view m as select a from t;
create unique index on m(a);
select ctid from pg_class where relname = 'm';
refresh materialized view concurrently m;
select ctid from pg_class where relname = 'm';

Before the patch, the ctid will change every time we refresh the matview.

--
Regards,
ChangAo Chen

---

From: David Geier <[email-masked]>
Date: Thu, 7 May 2026 17:46:19 +0200

> Before the patch, the ctid will change every time we refresh the matview.

Yeah, that's kind of what I had in mind as I wasn't expecting the
performance to matter much here. Avoiding the bloat seems generally
reasonable.

But refreshing a materialized view doesn't only change relispopulated
but also columns like relfilenode, relpages, relhasindex, etc. Doesn't
changing these columns during REFRESH MATERIALIZED VIEW make your
optimization applicable in a lot less cases?

I'm actually wondering why it works at all, even in the example you
gave. Because I thought that even when nothing has changed the pg_class
row is updated for more columns than just relispopulated.

--
David Geier

---

From: "cca5507" <[email-masked]>
Date: Fri, 8 May 2026 10:27:02 +0800

> optimization applicable in a lot less cases?

I don't think so. If we can skip SetMatViewPopulatedState(), we avoid generating
a dead pg_class tuple in all cases.

> row is updated for more columns than just relispopulated.

"refresh materialized view concurrently" is done by doing DELETE + INSERT
to the matview directly, so only relispopulated will change before the patch.
After the patch, the pg_class row don't change anymore.

--
Regards,
ChangAo Chen

---

From: "Zizhuan Liu" <[email-masked]>
Date: Thu, 28 May 2026 12:31:34 +0800

HI， ChangAo Chen, David Geier , hackers&nbsp;

&gt;&gt; But refreshing a materialized view doesn't only change relispopulated
&gt;&gt; but also columns like relfilenode, relpages, relhasindex, etc. Doesn't
&gt;&gt; changing these columns during REFRESH MATERIALIZED VIEW make your
&gt;&gt; optimization applicable in a lot less cases&nbsp;
&gt;
&gt;I don't think so. If we can skip SetMatViewPopulatedState(), we avoid generating
&gt;a dead pg_class tuple in all cases.
&gt;
&gt;&gt; I'm actually wondering why it works at all, even in the example you
&gt;&gt; gave. Because I thought that even when nothing has changed the pg_class
&gt;&gt; row is updated for more columns than just relispopulated.
&gt;
&gt;"refresh materialized view concurrently" is done by doing DELETE + INSERT
&gt;to the matview directly, so only relispopulated will change before the patch.
&gt;After the patch, the pg_class row don't change anymore.

1.Regarding pg_class.relispopulated
This column was introduced in PostgreSQL 9.3 specifically for materialized views.&nbsp;
Conceptually, the state of "IsScannable" is equivalent to and fully dependent on relispopulated.
This flag(relispopulated) is also used across multiple features, including COPY/pg_repack/pg_dump.
For the definitions of the populated and scannable states of materialized views, please refer to the official documentation:
&nbsp; &nbsp; https://www.postgresql.org/docs/devel/sql-creatematerializedview.html
&nbsp; &nbsp; https://www.postgresql.org/docs/devel/sql-refreshmaterializedview.html

2.Current status
As ChangAo Chen&nbsp;mentioned and verified via tests, and based on my own research and testing,
I agree with this conclusion:
&nbsp; &nbsp; If pg_class.relispopulated does not actually change, there is *no need* to invoke SetMatViewPopulatedState().
Skipping this call will also avoid executing the subsequent call chain:
&nbsp; &nbsp; CatalogTupleUpdate() -&gt; simple_heap_update() -&gt; heap_update().
&nbsp; &nbsp; This helps prevent dead tuples from being generated, and also reduces overhead on underlying mechanisms
&nbsp; &nbsp; including locks, transactions, WAL (xlog), as well as IPC contention and synchronization.&nbsp;

3.Regarding updates to attributes in pg_class
&gt; But refreshing a materialized view doesn't only change relispopulated
&gt; but also columns like relfilenode, relpages, relhasindex, etc. Doesn't
&gt; changing these columns during REFRESH MATERIALIZED VIEW make your
&gt; optimization applicable in a lot less cases?
To clarify: SetMatViewPopulatedState() is dedicated solely to modifying relispopulated.&nbsp;
Columns such as relfilenode, relpages and relhasindex are updated by other separate routines.

4. About SetMatViewPopulatedState() and its caller
There are two possible ways to implement this optimization:
&nbsp; &nbsp; --The first approach is to refactor SetMatViewPopulatedState() to handle the check internally:
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;If ((Form_pg_class) GETSTRUCT(tuple))-&gt;relispopulated != newstate, then perform the actual update; otherwise, do nothing.
&nbsp; &nbsp; --The second approach is to perform the check inside the caller RefreshMatViewByOid(), as implemented in ChangAo Chen’s patch.
&nbsp; &nbsp; &nbsp; &nbsp; I also noticed that intorel_startup() in createas.c follows the same design pattern — letting the caller handle the check before invoking the callee.&nbsp;

5. A small suggestion
After further consideration, I suggest using the condition:
&nbsp; &nbsp; if (RelationIsPopulated(matviewRel) != !skipData)
This is much more readable and intuitive compared to the original:
&nbsp; &nbsp; if (RelationIsPopulated(matviewRel) == skipData)
Especially when paired with the original call:
&nbsp; &nbsp; SetMatViewPopulatedState(matviewRel, !skipData);

Additionally, avoiding unnecessary updates and thus preventing dead tuples is especially valuable in BI/OLAP environments that use many materialized views and refresh them frequently (daily or hourly).

6. About Testing
--The new patch passes all PostgreSQL regression tests:
&nbsp; xman@xman_lzz:~/patch_1/build/testrun/regress/regress$ grep -n matview regression.out
128:# parallel group (20 tests): &nbsp;init_privs drop_operator security_label password lock object_address tablesample collate groupingsets replica_identity spgist gin matview identity gist generated_stored rowsecurity brin join_hash privileges
137:ok 126 &nbsp; &nbsp; &nbsp; + matview &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 47396 ms
xman@xman_lzz:~/patch_1/build/testrun/regress/regress$

--I have also checked the output files and expected files for the materialized view regression tests, and they are identical:
&nbsp; xman@xman_lzz:~$ ls -l /home/xman/patch_1/postgres/src/test/regress/expected/matview.out
-rw-rw-r-- 1 xman xman 24946 May 25 22:29 /home/xman/patch_1/postgres/src/test/regress/expected/matview.out
xman@xman_lzz:~$ ls -l /home/xman/patch_1/build/testrun/regress/regress/results/matview.out
-rw-rw-r-- 1 xman xman 24946 May 27 23:05 /home/xman/patch_1/build/testrun/regress/regress/results/matview.out
xman@xman_lzz:~$ diff /home/xman/patch_1/postgres/src/test/regress/expected/matview.out /home/xman/patch_1/build/testrun/regress/regress/results/matview.out
xman@xman_lzz:~$ echo $?
0
xman@xman_lzz:~$&nbsp;

The above is what I have found so far. Please feel free to correct me if I have any misunderstandings.
Thanks,
--
Zizhuan Liu&nbsp;(X-MAN)&nbsp;
[email-masked]

---

From: "cca5507" <[email-masked]>
Date: Thu, 18 Jun 2026 17:28:17 +0800

Hi Zizhuan,

Thanks for your careful review!

>     if (RelationIsPopulated(matviewRel) != !skipData)
>     if (RelationIsPopulated(matviewRel) == skipData)
>     SetMatViewPopulatedState(matviewRel, !skipData);

Both are ok for me.

--
Regards,
ChangAo Chen