Does Postgresql 10 query partitions in parallel?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Does Postgresql 10 query partitions in parallel?

Maltsev Eduard
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.

Thank you.
Eddie
Reply | Threaded
Open this post in threaded view
|

Re: Does Postgresql 10 query partitions in parallel?

John R Pierce
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.   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
Reply | Threaded
Open this post in threaded view
|

Re: Does Postgresql 10 query partitions in parallel?

Andres Freund
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

Reply | Threaded
Open this post in threaded view
|

Re: Does Postgresql 10 query partitions in parallel?

John R Pierce
On 12/6/2017 11:33 AM, Andres Freund wrote:
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
Reply | Threaded
Open this post in threaded view
|

Re: Does Postgresql 10 query partitions in parallel?

Thomas Kellerer
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 is 2

In 9.6 the default was 0

Thomas







Reply | Threaded
Open this post in threaded view
|

Re: Does Postgresql 10 query partitions in parallel?

Thomas Kellerer
In reply to this post by Maltsev Eduard
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 whatever gets pushed down to the foreign server might be executed in parallel (subject to the restrictions on parallel query on _that_ server).
I don't think requests to multiple foreign servers are executed in parallel though.

Thomas



Previous Thread Next Thread