title: REFRESH MATERIALIZED VIEW suddenly taking more time to complete
body: <p>We have a materialized view in our Postgres DB (11.12, managed by AWS RDS). We have a scheduled task that updates it every 5 minutes using <code>REFRESH MATERIALIZED VIEW &lt;view_name&gt;</code>. At some specific point last week, the time needed to refresh the view suddenly went from ~1s to ~20s. The view contains ~70k rows, with around 15 columns, all of them being integers, booleans or UUIDs.</p>
  <p>Prior to this:</p>
  <ul>
  <li>There were no changes in the server configuration.</li>
  <li>There were no changes to the view itself. In fact, running <code>EXPLAIN ANALYZE &lt;expression used to create the view&gt;</code> returns that the query still gets executed in less than a second. If the query is ran with a client like Postico, it takes ~20s to fetch all the results (a bit consistent with the time needed to materialize it, although we assume this is due to the time needed for network transmission).</li>
  <li>There were no changes in the schema or any significant record increase in the contents of the tables needed to compute the view.</li>
  <li>RDS Performance Insights indicate that the query is mostly using CPU resources</li>
  </ul>
  <p>I know this is probably not enough to get a solution, but:</p>
  <ul>
  <li>Are there any server performance metrics or logs that could lead us to understand better this situation?</li>
  <li>Is this just the normal time the server needs to persist the view to disk? If so, any idea of possible reasons why it started to take so long recently?</li>
  </ul>
  <p><a href="https://explain.depesz.com/s/pBAy" rel="nofollow noreferrer">Here</a> is a link to the execution plan.</p>
  <p>EDIT: creating another materialized view with the same <code>JOINS</code> but selecting less columns performs as expected (~1s).</p>
  <p>EDIT 2: setting <code>enable_nestloop = false</code> greatly speeds up the <code>REFRESH</code> operation (same performance as before). Would this indicate that refactoring the underlying query could solve the issue?</p>