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.
Thank you.
Eddie
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
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
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
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:
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
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