Parallel Sequential Scan on SELECT statement, but not on SELECT ... INTO [duplicate]

Question

This question already has answers here:

Insert result of a parallellized select query into a table, in Postgresql (2 answers)

Closed 7 years ago.

The following query

EXPLAIN
SELECT c.global_id
FROM centers_res17 AS c
LEFT JOIN regions_res17 AS r ON ST_Contains(r.wkb_geometry, c.wkb_geometry)
WHERE r.global_id IS NULL;

says that a Parallel Sequential Scan on centers_res17 will be used. This is good and the query is fast (8 workers are used, and the CPU is busy)

Now, if I do a SELECT c.global_id INTO foo the query plan indicates a Sequential Scan, which is much slower.

I tried to:

• CREATE the foo table before (also as a TEMP)
• Wrap the above statement in a WITH clause and then select its results into foo.
• CREATE foo AS (^^Above statement^^)

None of the above uses multiple workers to do a Parallel Sequential Scan (according to the query plan and actually running the query). I could just make the select statement 'as is' and then copy-paste the values from my results window ... not optimal.

Please explain to me, why the Parallel Sequential Scan is not used, or how to modify my query to have the results selected into foo.

I am using PostgreSQl 9.6 on Win10.

• Michael: To run my query with multiple workers, I copy the results to a file as discussed here stackoverflow.com/questions/1517635/…

Answer 1

Quote from the manual

The planner will not generate them for a given query if any of the following are true

• The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within a CTE, no parallel plans for that query will be generated. This is a limitation of the current implementation which could be lifted in a future release.

As you are inserting rows into a table, the underlying query will never use parallelism, at least not in current version (10) and earlier.

• Michael: understood. only read operations are parallel.