Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

Поиск
Список
Период
Сортировка
От Vijaykumar Jain
Тема Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?
Дата
Msg-id CAM+6J951DRJDRYC-78YaN9ak38SpVxmw2a6FtL9rTFjYesDT7w@mail.gmail.com
обсуждение исходный текст
Ответ на CREATE/REFRESH MATERIALIZED VIEW planner difference?  (Philip Semanchuk <philip@americanefficient.com>)
Ответы Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?  (Philip Semanchuk <philip@americanefficient.com>)
Список pgsql-general
Дерево обсуждения
if you are not using it concurrently, can you confirm the there are *no active* queries on the mv.
refresh requires AccessExclusiveLock and will wait, till it gets one.
just asking if you can rule out the extended time is not due to waiting for lock.

also, can you share the plans  where you see the diff.





On Tue, 1 Jun 2021 at 23:30, Philip Semanchuk <philip@americanefficient.com> wrote:
Hi all,
Should I expect a planner difference between CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW? We have a materialized view that uses 4 workers during CREATE but only one worker during REFRESH, and as a result the refresh takes much longer (~90 minutes vs. 30 minutes for the CREATE). So far this behavior has been 100% consistent.

I'm running both the CREATE and REFRESH on the same server (Postgres 11.9 on AWS Aurora). I don't think the refresh is using one worker in response to other things happening on the server because we’ve observed this happening when the server is not busy. We're not using the CONCURRENTLY option for REFRESH.

THanks
Philip



--
Thanks,
Vijay
Mumbai, India

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

Предыдущее
От: Philip Semanchuk
Дата:
Сообщение: CREATE/REFRESH MATERIALIZED VIEW planner difference?
Следующее
От: Philip Semanchuk
Дата:
Сообщение: Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?