0

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without creating the materialized view, it shows that said query is parallel aware, and executing said query by itself shows multiple processors active using standard linux tools like top. Conversely, executing the query within a REFRESH or CREATE materialized view wrapper, does not show multiple processes, and using EXPLAIN on CREATE MATERIALIZED VIEW shows a query that is not parallel aware. Is this the intentional behavior and why? I was not able to find any information on this. (PostgreSQL 10.5 on CentOS 7)

EDIT: Checked that this behavior is the same when using CREATE TABLE AS and SELECT INTO. Is there any way to write a table using parallelism?

EDIT 2: Is there any work-around I can use to CREATE TABLE using parallelism without first outputting the results of query to a file? Such as somehow splitting the query into several hundred equal parts defined by a aggregation variable in the data, followed by a specified number of cores in parts getting INSERT'ed in parallel?

0

Seems like this is a feature of Postgres 11: https://www.postgresql.org/docs/current/release-11.html#id-1.11.6.6.4

| improve this answer | |
  • Specifically, that link states that "Parallelization is now possible in...CREATE MATERIALIZED VIEW", which I can confirm having just tried it. It does not, however, appear to be the case with REFRESH MATERIALIZED VIEW, meaning that depending on your query it might be significantly faster to drop and re-create the view, assuming your workflow/application is OK with the view not existing while it is re-created. – ibrewster Jan 15 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.