Re: Mat view sometimes taking 10x the time to refresh concurrently

possible scenarios:

The tables in the view have much more data now than it was earlier. if you
track metrics for table size/db size, do you see growth there,
the tables and/or indexes may be bloated. you can vacuum / pg_repack on
tables used in the view and indexes if the bloat is too much.

can you run the query directly and see the explain plan. it might give some
hints if estimates are off.
Does the db server have enough resources ? you can try
bumping maintenance_work_mem and work_mem in a session and see if this
helps speed things up.

On Thu, 27 May 2021 at 21:45, Wells Oliver <wells(dot)oliver(at)gmail(dot)com> wrote:

> Hey, we have a mat view that normally takes ~18m to re-materialize
> concurrently, which we do daily. Maybe once a week, it takes ~180m to
> refresh, and we're at a loss as to why.
>
> We are running backs during the same general time window, where we do
> backup the schema where this mat view is located, could this be an issue?
> We have not noticed any lock errors in the backup or log, though.
>
> Any tips on tracing this down would be appreciated.
>
> pg 13.2
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>

--
Thanks,
Vijay
Mumbai, India