3

The following PostGIS query executes in parallel using Postgres 9.6's new parallel processing feature:

SELECT pnts.mb_code11, bdys.mb_code11
FROM testing.mb_random_points AS pnts
INNER JOIN testing.mb_2011_aust AS bdys ON ST_Contains(bdys.geom, pnts.geom);

Here's the query plan:

enter image description here

Run the same query as an insert, and it only runs on a single CPU:

INSERT INTO test
SELECT pnts.mb_code11, bdys.mb_code11
FROM testing.mb_random_points AS pnts
INNER JOIN testing.mb_2011_aust AS bdys ON ST_Contains(bdys.geom, pnts.geom);

Here's it's query plan:

enter image description here

How can I get the INSERT to run in parallel?

  • Try parallel DELETE. – Manoj Kumar Oct 3 '16 at 17:36
5

Writing data and reading data is a different story. Modifying data from different parallel resources always needs to be serialized and prepared for changes in source data while writing. It is therefore more complicated and needs to be treated carefully while reading data in parallel is no trouble.

Also see the wiki page for parallel queries.

Even when parallel query is enabled in general, the query planner will never generate a parallel plan if any of the following are true:

  • The query writes data. 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.
  • [...]

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.