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

Поиск
Список
Период
Сортировка
От Vijaykumar Jain
Тема Re: Mat view sometimes taking 10x the time to refresh concurrently
Дата
Msg-id CAM+6J94wb1wHf+c_52TUn+u=M2+szKQMbe5uhUnFBSkjmAuejg@mail.gmail.com
обсуждение исходный текст
Ответ на Mat view sometimes taking 10x the time to refresh concurrently  (Wells Oliver <wells.oliver@gmail.com>)
Список pgsql-admin
Дерево обсуждения
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@gmail.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

--


--
Thanks,
Vijay
Mumbai, India

В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Mat view sometimes taking 10x the time to refresh concurrently
Следующее
От: Wells Oliver
Дата:
Сообщение: Re: Mat view sometimes taking 10x the time to refresh concurrently