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.oliver@xxxxxxxxx> 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.
Thanks,
Vijay
Mumbai, India
