Parallel Sequential Scan Commited

In previous articles, I wrote about getting a parallel sequential scan that is committed in PostgreSQL 9.5. But that did not happen. However, I will be happy to tell you about the first parallel sequential scan commit in PostgreSQL to 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 the PostgreSQL 9.4 release cycle, where I added dynamic workers working in the background and dynamic shared memory, the development of which continued in PostgreSQL 9.5, where there was an idea to add a fundamental infrastructure for concurrency that was commited today. 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 do is to thank those 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 many 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 thanks to Noah Misch, who helped me a lot in the early stages of this project, when I struggled with problems in finding a way to solve them. Thirdly, I would like to thank the PostgreSQL community and the individuals who supported the review and test patches, the proposed improvements, and many others who supported us in many ways.

It is also important to say thanks to EnterpriseDB, in particular to its management. First, Tom Kincaid and Marc Linster. Without their support, it would be impossible for Ammit's and I to devote a lot of time to this project, as well as without my team at EnterpriseDB, who patiently covered me whenever it was necessary to solve other working issues. Thanks to everyone.

Now, time for a 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's what the plan looks like:

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 the workers are launched in an additional plan in parallel. Because the additional Parallel Seq Scan plan is an ordinary Seq Scan. Workers are coordinated with each other so that each block in relation is scanned 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 does not currently work for the inheritance hierarchy (using shared partitioning of tables), because it can be added between nodes. It is also not possible to push join down into the workers at this time. The infrastructure executor supports the launch of each type of plan, but the current scheduler is too stupid to support this. I hope to fix this problem before the end of the 9.6 release cycle. Given the current state of things, using this feature will give an advantage where adding an index no longer helps and adding a few workers will help increase execution speed.

My experience also says that adding a few workers usually helps, and the advantage does not scale well with a large number of workers. A deeper study 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 restriction. However, I would like to improve them further, as the number of CPUs has been growing all this time.

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