> On Jun 1, 2021, at 2:20 PM, Vijaykumar Jain <vijaykumarjain.git...@gmail.com>
> wrote:
>
> 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.
I can confirm that it’s not waiting on a lock. In addition, through the AWS CPU
utilization monitor I can see that the REFRESH uses one CPU/worker whereas the
CREATE uses four. This is consistent with the EXPLAIN ANALYZE for the CREATE
which says it uses four workers.
> also, can you share the plans where you see the diff.
Unless I misunderstand, there is no plan for a REFRESH.
EXPLAIN (ANALYZE, BUFFERS) refresh materialized view my_mat_view
+-------------------------------------------+
| QUERY PLAN |
|-------------------------------------------|
| Utility statements have no plan structure |
+-------------------------------------------+
Cheers
Philip
>
> On Tue, 1 Jun 2021 at 23:30, Philip Semanchuk <phi...@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
