From: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
To: Wells Oliver <wells.oliver@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: work_mem + refresh mat view concurrently performance
Date: Thu, 22 Jun 2023 16:49:47 +0000
Message-ID: <8414ad71bc6d48c0864a944960b87aec@express-scripts.com> (raw)
In-Reply-To: <CAOC+FBWx7nWPWZdiEMGPOFv9dWZvJK=xu_6WjNpvdcZwYsD5Uw@mail.gmail.com>
References: <CAOC+FBWx7nWPWZdiEMGPOFv9dWZvJK=xu_6WjNpvdcZwYsD5Uw@mail.gmail.com>
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.
--
Wells Oliver
wells.oliver@gmail.com<mailto:wellsoliver@gmail.com>
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: pgsql-admin@postgresql.org
Cc: Matthew.Wetmore@express-scripts.com, wells.oliver@gmail.com
Subject: Re: work_mem + refresh mat view concurrently performance
In-Reply-To: <8414ad71bc6d48c0864a944960b87aec@express-scripts.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox