Re: materialized view refresh of a foreign table

Поиск
Список
Период
Сортировка
От Rick Otten
Тема Re: materialized view refresh of a foreign table
Дата
Msg-id CAMAYy4+aPpDpcbnzNdz6s3cRmXXYs1FDTrhJkuhVnjBV8DbLog@mail.gmail.com
обсуждение исходный текст
Ответ на materialized view refresh of a foreign table  (Rick Otten <rottenwindfish@gmail.com>)
Список pgsql-performance
Дерево обсуждения


On Sun, Jun 23, 2019 at 10:21 AM Rick Otten <rottenwindfish@gmail.com> wrote:
I'm not sure where else to look, so I'm asking here for tips.

I have a table in a remote (Heroku-managed) postgresql database (PG 10.7).

On the other end, (PG 11.3) I have a foreign table configured with a materialized view in front of it.

Up until Thursday evening, it was taking about 12 - 15 seconds to refresh, it is only 15,000 rows with 41 columns.   Since Thursday evening it has been taking 15 _minutes_ or more to refresh.  Nothing changed on my end that I'm aware of.  It completes, it just takes forever.


I believe I've solved this mystery.  Thanks for hearing me out.  Just the opportunity to summarize everything I'd tried helped me discover the root cause.

In the middle of the table there is a 'text' column.   Since last Thursday there were a number of rows that were populated with very long strings.  (lots of text in that column).   This appears to have completely bogged down the materialized view refresh.  Since we weren't using that column in our analytics database at this time, I simply removed it from the materialized view.  If we do end up needing it, I'll give it its own materialized view and/or look at chopping up the text into just the bits we need.


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

Предыдущее
От: Steven Winfield
Дата:
Сообщение: RE: scans on table fail to be excluded by partition bounds
Следующее
От: Hugh Ranalli
Дата:
Сообщение: Perplexing, regular decline in performance