1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
nmakb
  • 1,069
  • 1
  • 17
  • 35
  • 1
    this page maybe help? https://www.postgresql.org/docs/11/when-can-parallel-query-be-used.html – spike 王建 Aug 19 '20 at 04:56
  • 1
    no, unfortunately. I personally drop and recreate (+ all the dependent objects) the heavy materialized views, and refresh the simpler ones – JGH Aug 19 '20 at 14:46
  • Thanks @JGH for sharing, I ended up doing the same thing. – nmakb Aug 20 '20 at 02:50
  • @nmakb.Talking something else, in my opinion, I think there are no much difference between read-only table and materialized view with same structure.Do you think is there any promotion in materialized view than table in practise? – spike 王建 Aug 20 '20 at 02:55
  • @spikle, yes there is difference. If you use read-only table, and you want to refresh it - you would need to create new one and rename it to orig if you want to keep orig available for users. If you drop orig read only table then while its getting recreated it wont be available. Both the scenarios are taken care when you use materialized view as it offers concurrent refresh feature as well. – nmakb Aug 20 '20 at 03:03
  • Sorry, I misrepresentation my meaning.The read-only table, I mean you can not delete or create records manaully,it only will be refreshed by triggers or something else.I think concurrent refresh maybe table also support – spike 王建 Aug 20 '20 at 03:11

1 Answers1

3

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.

alfonx
  • 6,936
  • 2
  • 49
  • 58