2

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 either 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 parallel query and then its result inserted into a table?

1

I think the only even worth trying is to use PostgreSQL FDW which may or may not work. Your use case would also have to be super-niche for that to pay off, like a complex GIS aggregation query.

You can see this restriction is still very much noted in the code,

The most significant restriction imposed by parallel mode is that all operations must be strictly read-only; we allow no writes to the database and no DDL. We might try to relax these restrictions in the future.

And, again here

 * Don't allow writes in parallel mode.  Supporting UPDATE and DELETE
 * would require (a) storing the combocid hash in shared memory, rather
 * than synchronizing it just once at the start of parallelism, and (b) an
 * alternative to heap_update()'s reliance on xmax for mutual exclusion.
 * INSERT may have no such troubles, but we forbid it to simplify the
 * checks.

I think it's being stopped by a call to ExecCheckXactReadOnly here in ExecCheckXactReadOnly

if (plannedstmt->commandType != CMD_SELECT || plannedstmt->hasModifyingCTE)
    PreventCommandIfParallelMode(CreateCommandTag((Node *) plannedstmt));

Shy of that Maybe in PostgreSQL 11, or 12?

|improve this answer|||||
0

Parallelism can be used read part of query. For example,

insert into t1 select * from parallel_exec();

Now if there is some statement in parallel_exec() function, that can use parallelism, such as a select statement then you can do this:

Example function definition which can use parallelism:

create or replace function parallel_exec() returns integer
as $$
begin
        Perform * from t1 where c1 >= 10 and c1 < 11;
return 1;
end;
$$ language plpgsql STABLE PARALLEL SAFE;
|improve this answer|||||

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.