public inbox for [email protected]
help / color / mirror / Atom feedFrom: =?utf-8?B?Wml6aHVhbiBMaXU=?= <[email protected]>
To: =?utf-8?B?Y2NhNTUwNw==?= <[email protected]>
To: =?utf-8?B?RGF2aWQgR2VpZXI=?= <[email protected]>
To: =?utf-8?B?cGdzcWwtaGFja2Vycw==?= <[email protected]>
Cc: =?utf-8?B?5oiR6Ieq5bex55qE6YKu566x?= <[email protected]>
Subject: Re: Avoid calling SetMatViewPopulatedState if possible
Date: Thu, 28 May 2026 12:31:34 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
HI， ChangAo Chen, David Geier , hackers
>> 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 don't think so. If we can skip SetMatViewPopulatedState(), we avoid generating
>a dead pg_class tuple in all 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.
>
>"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.
1.Regarding pg_class.relispopulated
This column was introduced in PostgreSQL 9.3 specifically for materialized views.
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:
https://www.postgresql.org/docs/devel/sql-creatematerializedview.html
https://www.postgresql.org/docs/devel/sql-refreshmaterializedview.html
2.Current status
As ChangAo Chen mentioned and verified via tests, and based on my own research and testing,
I agree with this conclusion:
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:
CatalogTupleUpdate() -> simple_heap_update() -> heap_update().
This helps prevent dead tuples from being generated, and also reduces overhead on underlying mechanisms
including locks, transactions, WAL (xlog), as well as IPC contention and synchronization.
3.Regarding updates to attributes in pg_class
> 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?
To clarify: SetMatViewPopulatedState() is dedicated solely to modifying relispopulated.
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:
--The first approach is to refactor SetMatViewPopulatedState() to handle the check internally:
If ((Form_pg_class) GETSTRUCT(tuple))->relispopulated != newstate, then perform the actual update; otherwise, do nothing.
--The second approach is to perform the check inside the caller RefreshMatViewByOid(), as implemented in ChangAo Chen’s patch.
I also noticed that intorel_startup() in createas.c follows the same design pattern — letting the caller handle the check before invoking the callee.
5. A small suggestion
After further consideration, I suggest using the condition:
if (RelationIsPopulated(matviewRel) != !skipData)
This is much more readable and intuitive compared to the original:
if (RelationIsPopulated(matviewRel) == skipData)
Especially when paired with the original call:
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:
xman@xman_lzz:~/patch_1/build/testrun/regress/regress$ grep -n matview regression.out
128:# parallel group (20 tests): 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 + matview 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:
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:~$
The above is what I have found so far. Please feel free to correct me if I have any misunderstandings.
Thanks,
--
Zizhuan Liu (X-MAN)
[email protected]
Attachments:
[application/octet-stream] v2-0001-Avoid-calling-SetMatViewPopulatedState-if-possibl.patch (957B, ../[email protected]/3-v2-0001-Avoid-calling-SetMatViewPopulatedState-if-possibl.patch)
download | inline diff:
From 7784456ce74c98c042791a492741700e8d1523f4 Mon Sep 17 00:00:00 2001
From: "Zizhuan Liu(X-MAN)" <[email protected]>
Date: Thu, 28 May 2026 12:19:41 +0800
Subject: [PATCH v2] Avoid calling SetMatViewPopulatedState if possible
---
src/backend/commands/matview.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index f7d8007..d090bad 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -296,7 +296,8 @@ RefreshMatViewByOid(Oid matviewOid, bool is_create, bool skipData,
* Tentatively mark the matview as populated or not (this will roll back
* if we fail later).
*/
- SetMatViewPopulatedState(matviewRel, !skipData);
+ if (RelationIsPopulated(matviewRel) != !skipData)
+ SetMatViewPopulatedState(matviewRel, !skipData);
/* Concurrent refresh builds new data in temp tablespace, and does diff. */
if (concurrent)
--
2.43.0
view thread (8+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Avoid calling SetMatViewPopulatedState if possible
In-Reply-To: <[email protected]>
* 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