Introduction
All of you know that a new PostgreSQL 10 will soon appear. With this article we begin a series of articles about the most important changes that we believe have appeared in PostgreSQL 10.
In PostgreSQL 9.6, developers implement parallel sequential scan, but you had to choose between using parallelism and using your indexes. In the 10th version, the community continues to develop this direction and that’s what new features have appeared.
Creating Test Data
postgres=# CREATE TABLE foo AS SELECT row_number() OVER() AS id, generate_series%100 AS c_100, generate_series%500 AS c_500 FROM generate_series(1,20000000); SELECT 20000000 postgres=# ALTER TABLE foo ADD CONSTRAINT pk_foo PRIMARY KEY (id); ALTER TABLE postgres=# CREATE INDEX idx_foo ON foo (c_100); CREATE INDEX
Parallel Index-Only Scan and Parallel Index Scan
Parallel queries are now also used for Index Scan and Index Only Scan. Infrastructure has been added to allow index access methods to support parallel scans, the btree access method has been modified to use this infrastructure, and the optimizer and executor have been taught to use these capabilities for index scans and index only scans. Therefore, it’s now possible for the driving table to be scanned using an index-scan or an index-only scan.
postgres=# EXPLAIN SELECT count(*) FROM foo WHERE id >10 AND id < 500000; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=16984.68..16984.69 rows=1 width=8) -> Gather (cost=16984.47..16984.68 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=15984.47..15984.48 rows=1 width=8) -> Parallel Index Only Scan using pk_foo on foo (cost=0.44..15457.48 rows=210795 width=0) Index Cond: ((id > 10) AND (id < 500000)) (6 rows)
and Parallel Index Scan
postgres=# EXPLAIN SELECT count(c_100) FROM foo WHERE id < 5000000; QUERY PLAN -------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=146317.19..146317.20 rows=1 width=8) -> Gather (cost=146316.98..146317.19 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=145316.98..145316.99 rows=1 width=8) -> Parallel Index Scan using pk_foo on foo (cost=0.44..140112.10 rows=2081952 width=4) Index Cond: (id < 5000000) (6 rows)
Parallel Bitmap Heap Scan
In PostgreSQL 10 the driving table of a parallel query must be scanned with a parallel-aware scan type capable of partitioning the scan among the available workers. With a parallel bitmap heap scan, one process scans the index and builds a data structure in shared memory indicating all of the heap pages that need to be scanned, and then all cooperating processes can perform the heap scan in parallel. It would be nice to be able to perform the index scan in parallel, too, but that will have to wait for a future release.
Let’s see how it works. We will calculate the count of rows grouped by c_100 field:
postgres=# SELECT count(*), c_100 FROM foo WHERE c_100 <10 GROUP BY c_100; count | c_100 --------+------- 200000 | 0 200000 | 1 200000 | 2 200000 | 3 200000 | 4 200000 | 5 200000 | 6 200000 | 7 200000 | 8 200000 | 9 (10 rows)
Returns 200k rows for each values by c_100 field. Look at the query plan:
postgres=# EXPLAIN SELECT count(*), c_100 FROM foo WHERE c_100 <10 GROUP BY c_100; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=160812.93..160815.43 rows=100 width=12) Group Key: c_100 -> Sort (cost=160812.93..160813.43 rows=200 width=12) Sort Key: c_100 -> Gather (cost=160784.28..160805.28 rows=200 width=12) Workers Planned: 2 -> Partial HashAggregate (cost=159784.28..159785.28 rows=100 width=12) Group Key: c_100 -> Parallel Bitmap Heap Scan on foo (cost=37189.14..155645.38 rows=827780 width=4) Recheck Cond: (c_100 < 10) -> Bitmap Index Scan on idx_foo (cost=0.00..36692.47 rows=1986671 width=0) Index Cond: (c_100 < 10) (12 rows)
SubPlan
In PostgreSQL 10 a table with an uncorrelated subplan can appear in the parallel portion of the plan. Unfortunately, a table with a correlated subplans or a reference to an InitPlan is still not able to be considered for parallelism.
postgres=# postgres=# EXPLAIN SELECT * FROM foo WHERE c_100 NOT IN (SELECT c_500 FROM foo GROUP BY c_500 HAVING c_500 <5); QUERY PLAN ----------------------------------------------------------------------------------------------------------- Seq Scan on foo (cost=213669.94..571779.51 rows=10000023 width=16) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Group (cost=213663.69..213668.69 rows=500 width=4) Group Key: foo_1.c_500 -> Sort (cost=213663.69..213666.19 rows=1000 width=4) Sort Key: foo_1.c_500 -> Gather (cost=213508.86..213613.86 rows=1000 width=4) Workers Planned: 2 -> Partial HashAggregate (cost=212508.86..212513.86 rows=500 width=4) Group Key: foo_1.c_500 -> Parallel Seq Scan on foo foo_1 (cost=0.00..212275.91 rows=93181 width=4) Filter: (c_500 < 5) (13 rows)
Parallel Merge Join / Gather Merge
Parallel queries are now available even when Merge Join is chosen. Gather Merge which gathers results while Merge by parallel processing is now available. In some sense, these are not truly parallel hash joins, because while each participant sees only a subset of the rows on the outer side of the join, each participant must visit every row on the inner side of the join; otherwise, the join results might be incomplete. This means that the work on the outer side of the join is divided among the participants, but the work on the inner side of the join is duplicated by every participant.
postgres=# EXPLAIN SELECT count(*) FROM foo f1 INNER JOIN foo f2 ON f2.c_100 = f1.c_100; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=29245498365.42..29245498365.43 rows=1 width=8) -> Gather (cost=29245498365.20..29245498365.41 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=29245497365.20..29245497365.21 rows=1 width=8) -> Merge Join (cost=0.88..25067827206.76 rows=1671068063377 width=0) Merge Cond: (f1.c_100 = f2.c_100) -> Parallel Index Only Scan using idx_foo on foo f1 (cost=0.44..835129.87 rows=8333352 width=4) -> Materialize (cost=0.44..1001796.92 rows=20000046 width=4) -> Index Only Scan using idx_foo on foo f2 (cost=0.44..951796.81 rows=20000046 width=4) (9 rows)
PREPARE / EXECUTE statement
Parallel queries can now be executed even in search processing using PREPARE and EXECUTE statements.
postgres=# PREPARE pr_1 AS SELECT count(*) FROM foo WHERE id < 5000000; PREPARE postgres=# EXPLAIN EXECUTE pr_1; QUERY PLAN -------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=146317.19..146317.20 rows=1 width=8) -> Gather (cost=146316.98..146317.19 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=145316.98..145316.99 rows=1 width=8) -> Parallel Index Scan using pk_foo on foo (cost=0.44..140112.10 rows=2081952 width=0) Index Cond: (id < 5000000) (6 rows)
Conclusion
In the PostgreSQL 10, query parallelism continues to evolve. A lot of work has been done in this direction, but it is too early to say that all work is over. All these improvements should speed up the execution of database queries, and also make the query planner even more advanced.