My experience on a large mat view, if you have parallel set up, drop the mat view and recreate since CREATE is available for parallel.
It’s the concurrently that slows things down without the locks.
I would try session work_men in sandbox first, depending on how long the view refresh takes, as a sharp increase could affect memory for other stuff or have server impact
From: Wells Oliver <wells.oliver@gmail.com>
Sent: Thursday, June 22, 2023 9:02 AM
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: [EXTERNAL] work_mem + refresh mat view concurrently performance
Is there any relationship here? Wondering if increasing the former to a large amount for a session (2gb?) would help the joins/hashing/etc of the mat view refresh. Or if this is just nonsense.
Looking for some gains in refreshing if possible.
--
