0

According to https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html,

"Even when it is in general possible for parallel query plans to be generated, 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."

Indeed, when I try to insert result of a parallel SELECT query into a table ( either by SELECT.. INTO or by WITH..SELECT..INTO ), the query is not executed as parallel query.

My question is: Is there any way to trick the Postgresql so that a SELECT query is executed as a parallel query and then its result inserted into a table?

Improve this question
2
  • 3
    As I said before the deprecated SELECT ... INTO .. is a DDL statement and there is no way to make Postgres use parallel execution for DDL (or DML) statements. Commented Nov 3, 2017 at 9:36
  • 1
    This is not related to the true-[PARALLEL] process scheduling. Even the "just"-[CONCURRENT] process execution is restricted from doing "promised" query-plan, because the implementing engine simply rejects any attempt, which would go into resolving the "just"-[CONCURRENT]-update-propagations beyond the scope of safe-mods ( and all other non-[SERIAL] transactions ( modifications ) were not implemented in the engine to run safely under the "promised" query-plan --- so they rather get refused, or re-arranged into an old-&-safe pure-[SERIAL] transaction ordering ). "Tricking" is risky ... Commented Nov 3, 2017 at 10:36

1 Answer 1

Reset to default
0

There is a trick with psql -o parameter.

i.e.

Step1:

call psql -h localhost -d dbname -U username -c "select * from vw_FBigTable_extract" -o FBigTable_extract.csv -A -t -F ","

Step2:

call psql -h localhost -d dbname -U username -c "COPY t_FBigTable_extract FROM 'FBigTable_extract.csv' WITH (FORMAT CSV, DELIMITER ',', HEADER FALSE, ENCODING 'windows-1252')"

Sometimes works faster then non-parallel approach.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.