Thread: Does Postgresql 10 query partitions in parallel?

I'm curious if the new feature of Postgresql allows to take advantage of multiple cpus on server, and multiple servers (fdw), for larger read only queries (Data mining). In general there should be some worker that queries partitions and merges the results, and I expect it to be done in parallel. This becomes critical when foreign tables are used, I suppose.

PostgreSQL 10 does indeed have a parallel query feature that will use multiple cores.  you have to explicity invoke it.   As this is a first implementation, its fairly limited as to the sorts of queries that can be parallized, but this will be enhanced in future versions.

https://www.postgresql.org/docs/current/static/parallel-query.html (read the whole chapter)

-- 
john r pierce, recycling bits in santa cruz

On 2017-12-06 10:53:22 -0800, John R Pierce wrote:
> On 12/6/2017 5:11 AM, Maltsev Eduard wrote:
> > I'm curious if the new feature of Postgresql allows to take advantage of
> > multiple cpus on server, and multiple servers (fdw), for larger read
> > only queries (Data mining). In general there should be some worker that
> > queries partitions and merges the results, and I expect it to be done in
> > parallel. This becomes critical when foreign tables are used, I suppose.
> 
> 
> PostgreSQL 10 does indeed have a parallel query feature that will use
> multiple cores.  you have to explicity invoke it.  

"you have to explicitly invoke it" -  huh?


> As this is a first
> implementation, its fairly limited as to the sorts of queries that can be
> parallized, but this will be enhanced in future versions.

It's already been expanded a lot since 9.6.

Greetings,

Andres Freund

PostgreSQL 10 does indeed have a parallel query feature that will use
multiple cores.  you have to explicity invoke it.  

"you have to explicitly invoke it" -  huh?

oops, I meant, enable.

-- 
john r pierce, recycling bits in santa cruz

John R Pierce schrieb am 06.12.2017 um 20:34:
>>> PostgreSQL 10 does indeed have a parallel query feature that will use
>>> multiple cores.  you have to explicity invoke it.
>> "you have to explicitly invoke it" -  huh?
> 
> oops, I meant, enable.

You are correct for 9.6 where the default was "disabled", but in 10 the default is, that it's enabled:

https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER

Sets the maximum number of workers that can be started by a single Gather or Gather Merge node [...] The default value
is2
 

In 9.6 the default was 0

Thomas

Maltsev Eduard schrieb am 06.12.2017 um 14:11:
> I'm curious if the new feature of Postgresql allows to take advantage
> of multiple cpus on server, and multiple servers (fdw), for larger
> read only queries (Data mining). In general there should be some
> worker that queries partitions and merges the results, and I expect
> it to be done in parallel. This becomes critical when foreign tables
> are used, I suppose.

Postgres 9.6 started supporting parallel queries and this has been extended in 10

This has not been "integrated" with the new declarative partitioning.
I think Postgres 11 will support parallel execution based on partitions.

However with foreign tables, a lot of work is pushed to the foreign server (e.g. joins, where clause, aggregation) and
whatevergets pushed down to the foreign server might be executed in parallel (subject to the restrictions on parallel
queryon _that_ server).
 
I don't think requests to multiple foreign servers are executed in parallel though.

Thomas