Parallel sequential scans are coming to PostgreSQL

It didn’t made it into the upcoming PostgreSQL 9.5 release but it will probably be coming in 9.6: Parallel sequential scans. If you take a look at the development documentation some new parameters showed up:

• max_parallel_degree: The maximum degree of parallelism for an individual parallel operation.
• parallel_tuple_cost: Sets the planner’s estimate of the cost of transferring a tuple from a parallel worker process to another process. The default is 0.1
• parallel_setup_cost: Sets the planner’s estimate of the cost of launching parallel worker processes. The default is 1000.

Lets do a quick test. If you want to setup a PostgreSQL instance from the nightly development snapshot you can follow this post.

As you can see below I am running a 9.6 development version:

postgres@oel7:/home/postgres/ [dummy] install/bin/psql -p 6666 postgres
Null display is "NULL".
Timing is on.
psql (9.6devel)
Type "help" for help.

For testing parallel sequential scans we need to raise the max_parallel_degree parameter which is zero by default:

(postgres@[local]:6666) [postgres] > show max_parallel_degree;
 max_parallel_degree 
---------------------
 0
(1 row)

Time: 0.302 ms
(postgres@[local]:6666) [postgres] > alter system set max_parallel_degree=5;
ALTER SYSTEM
Time: 5.509 ms

(postgres@[local]:6666) [postgres] > select pg_reload_conf();
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "max_parallel_degree" changed to "5"
 pg_reload_conf 
----------------
 t
(1 row)

Time: 19.252 ms

Note: The log messages displayed when the configuration was reloaded are new, too.

Now lets create some data to test with:

(postgres@[local]:6666) [postgres] > create table tt ( a int, b int );
CREATE TABLE

Time: 427.362 ms(postgres@[local]:6666) 
[postgres] > insert into tt (a,b) 
                values ( generate_series(1,1000000),generate_series(1,1000000));
INSERT 0 1000000
Time: 4912.641 ms

So, lets see parallel sequential scan in action:

(postgres@[local]:6666) [postgres] > explain analyze select count(*) from tt where a between 5 and 50000;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=13800.29..13800.30 rows=1 width=0) (actual time=94.067..94.068 rows=1 loops=1)
   ->  Gather  (cost=1000.00..13677.60 rows=49076 width=0) (actual time=0.655..91.073 rows=49996 loops=1)
         Number of Workers: 2
         ->  Parallel Seq Scan on tt  (cost=0.00..7770.00 rows=49076 width=0) (actual time=8.465..33.548 rows=16665 loops=3)
               Filter: ((a >= 5) AND (a <= 50000))
               Rows Removed by Filter: 316668 ab
 Planning time: 0.280 ms
 Execution time: 94.127 ms
(8 rows)

Note the “Number of Workers” and “Parallel Seq Scan” information in the execution plan. Looks promising … More posts about testing this in more detail to come.