Parallel Sequential Scan Commited

In previous articles I wrote about getting parallel sequential scan commited to PostgreSQL 9.5. But this did not happen. However, I will be happy to tell you about the first commit parallel sequential scan to PostgreSQL in the master branch for the upcoming release of PostgreSQL 9.6.

Parallel queries for PostgreSQL have been my dream for a long time, which I have been working on for several years. Their development began with PostgreSQL 9.4 release cycle, where I added dynamic workers working in the background and dynamic shared memory, the development of which was continued in PostgreSQL 9.5, where the idea of ​​adding a fundamental infrastructure for parallelism that was committed today was taking place. I would like to tell you a little about today's commits and about the work that will be done next.


')
But the first thing I would like to do is thank the ones I owe. Firstly, Amit Kapila, who made a huge contribution to the completion of the project. The two of us wrote a large amount of code, which became a full-fledged part of this functionality. And part of this code has gone through a lot of commits over the past few years. We also wrote a large amount of code that was not included in commits. Secondly, I want to say thank you to Noah Misch, who helped me well in the early stages of this project, when I struggled with problems in finding ways to solve them. Thirdly, I would like to say thanks to the PostgreSQL community and individuals who supported the review and test patches, suggested improvements, and many others who supported us in different ways.

It is also important to say thanks EnterpriseDB, in particular, its leadership. First, Tom Kincaid and Marc Linster. Without their support, it would not be possible for Ammit's and me to devote a lot of time to this project, as well as without my team at EnterpriseDB, who patiently covered me up whenever it was necessary to solve other work issues. Thank you all.

Now, time for demo:

rhaas=# \timing Timing is on. rhaas=# select * from pgbench_accounts where filler like '%a%'; aid | bid | abalance | filler -----+-----+----------+-------- (0 rows) Time: 743.061 ms 

 rhaas=# set max_parallel_degree = 4; SET Time: 0.270 ms rhaas=# select * from pgbench_accounts where filler like '%a%'; aid | bid | abalance | filler -----+-----+----------+-------- (0 rows) Time: 213.412 ms 

Here is the plan:

 rhaas=# explain (costs off) select * from pgbench_accounts where filler like '%a%'; QUERY PLAN --------------------------------------------- Gather Number of Workers: 4 -> Parallel Seq Scan on pgbench_accounts Filter: (filler ~~ '%a%'::text) (4 rows) 

The accumulating node collects all the workers, and all workers are started in the additional plan in parallel. Because the additional plan Parallel Seq Scan - ordinary Seq Scan. Workers are coordinated with each other so that each block is scanned with respect only once. Each worker can produce a subset of the final result set, and the collecting node collects the results from all.

One big enough limitation of the current implementation is that we generate node collectors on top of Parallel Seq Scan nodes. This means that this function currently does not work for the inheritance hierarchy (using split table partitioning), because it can be added between nodes. It is also not possible to push the join down into the workers at this time. The infrastructure provider supports launching plans of every type, but the current scheduler is too dumb to support this. I hope to fix this problem before the end of the release cycle 9.6. Taking into account the current state of affairs, the use of this feature will give an advantage where the addition of an index no longer helps, and the addition of several workers will help increase the speed of execution.

Also, my experience says that adding several workers as a rule helps, and the advantage does not scale well with a large number of workers. More research is also needed to understand why this is happening and how to improve it ... As you can see, even 5 workers can improve productivity quite a bit, but this is not as important as in the previous constraint. However, I would like to improve them further, as the number of CPUs is growing all the time.

In conclusion, I would like to point out that there are a number of specific tasks that must be completed before I can call this function even in its basic form completely complete. It is likely that there are still errors. Testing is very much appreciated. Please report the problems you find when testing at pgsql-hackers and postgresql.org. Thank.