CODEWITHSUNDEEP
postgresqlmaterialized-viewspostgresql-parallel-query
nmakb
nmakb

Reputation: 1235

Materialized view refresh in parallel

Created materialized view in parallel by setting max_parallel_workers_per_gather to 4. I could see parallel sessions kicking off and creating the mview much faster than serial execution (10mins - parallel vs over an hour - serial).

Now I want the refresh of the mview to happen taking around same time as create, I am okay with it taking little more than create, to execute the steps it takes when refreshing the view. But what I am noticing is, refresh is not running in parallel at all - even after having max_parallel_workers_per_gather to 4.

Does PostgreSQL 11.6 not support refresh of the mviews in parallel as it did while creating it?

Upvotes: 2

Views: 2768

Answers (1)

alfonx
alfonx

Reputation: 7216

Does PostgreSQL 11.6 not support refresh of the mviews in parallel as it did while creating it?

Exactly right: REFRESH (other than CREATE MV) cant use parallel workers in PG 11 ...

BUT the good news: PostgreSQL 14 supports it: Quoting from PostgreSQL 14 Feature Highlights

There are many improvements to query parallelism in PostgreSQL 14. In addition to overall performance improvements for parallel sequential scans, the RETURN QUERY directive in PL/pgSQL can now execute queries with parallelism. REFRESH MATERIALIZED VIEW can now use query parallelism as well.

Upvotes: 3

Related Questions