PostgreSQL 并行查詢概述

大資料時代，人們使用資料庫系統處理的資料量越來越大，請求越來越複雜，對資料庫系統的大資料處理能力和混合負載能力提出更高的要求。PostgreSQL 作為世界上最先進的開源資料庫，在大資料處理方面做了很多工作，如并行和分區。

PostgreSQL 從 2016 年釋出的 9.6 開始支援并行，在此之前，PostgreSQL 僅能使用一個程序處理使用者的請求，無法充分利用資源，亦無法很好地滿足大資料量、複雜查詢下的性能需求。2018 年 10 月釋出的 PostgreSQL 11，在并行方面做了大量工作，支援了并行哈希連接配接，并行

Append

以及并行建立索引等特性，對于分區表，支援了

Partition-wise JOIN

。

本文從以下三方面介紹 PostgreSQL 的并行查詢特性：

并行查詢基礎元件，包括背景工作程序（Background Work Process），動态共享記憶體（Dynamic Shared Memory）以及背景工作程序間的通信機制和消息傳遞機制
并行執行算子的實作，包括并行順序掃描、并行索引掃描等并行掃描算子，三種連接配接方式的并行執行以及并行 Append
并行查詢優化，介紹并行查詢引入的兩種計劃節點，基于規則計算背景工作程序數量以及代價估算

舉個例子

首先，通過一個例子，讓我們對 PostgreSQL 的并行查詢以及并行計劃有一個較宏觀的認識。如下查詢：統計人員表

people

中參加 2018 PostgreSQL 大會的人數：

SELECT COUNT(*) FROM people WHERE  inpgconn2018 = 'Y';

沒有開并行的情況下（

max_parallel_workers_per_gather=0

），查詢計劃如下：

Aggregate  (cost=169324.73..169324.74 rows=1 width=8) (actual time=983.729..983.730 rows=1 loops=1)
   ->  Seq Scan on people  (cost=0.00..169307.23 rows=7001 width=0) (actual time=981.723..983.051 rows=9999 loops=1)
         Filter: (atpgconn2018 = 'Y'::bpchar)
         Rows Removed by Filter: 9990001
 Planning Time: 0.066 ms
 Execution Time: 983.760 ms

開啟并行的情況下（

max_parallel_workers_per_gather=2

Finalize Aggregate  (cost=97389.77..97389.78 rows=1 width=8) (actual time=384.848..384.848 rows=1 loops=1)
   ->  Gather  (cost=97389.55..97389.76 rows=2 width=8) (actual time=384.708..386.486 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=96389.55..96389.56 rows=1 width=8) (actual time=379.597..379.597 rows=1 loops=3)
               ->  Parallel Seq Scan on people  (cost=0.00..96382.26 rows=2917 width=0)
                 (actual time=378.831..379.341 rows=3333 loops=3)
                     Filter: (atpgconn2018 = 'Y'::bpchar)
                     Rows Removed by Filter: 3330000
 Planning Time: 0.063 ms
 Execution Time: 386.532 ms

max_parallel_workers_per_gather

參數控制執行節點的最大并行程序數，通過以上并行計劃可知，開啟并行後，會啟動兩個 worker 程序（即

Workers Launched: 2

）并行執行，且執行時間（

Execution Time

）僅為不并行的40%。該并行計劃可用下圖表示：

并行查詢計劃中，我們将處理使用者請求的

backend

程序稱之為主程序（leader），将執行時動态生成的程序稱之為工作程序（worker）。每個 worker 執行

Gather

節點以下計劃的一個副本，leader 節點主要負責處理

Gather

及其以上節點的操作，根據 worker 數不同，leader 也可能會執行

Gather

以下計劃的副本。

并行基礎元件

PostgreSQL 從 9.4 和 9.5 已經開始逐漸支援并行查詢的一些基礎元件，如背景工作程序，動态共享記憶體和工作程序間的通信機制，本節對這些基礎元件做簡要介紹。

背景工作程序（Background Worker Process）

PostgreSQL 是多程序架構，主要包括以下幾類程序：

守護程序，通常稱之為 postmaster 程序，接收使用者的連接配接并 fork 一個子程序處理使用者的請求
backend 程序，即 postmaster 建立的用于處理使用者請求的程序，每個連接配接對應一個 backend 程序
輔助程序，用于執行 checkpoint ，背景刷髒等操作的程序
背景工作程序，用于執行特定任務而動态啟動的程序，如上文提到的 worker 程序

上圖中 server process 即

postmaster

程序，在核心中，

postmaster

與

backend

程序都是

postgres

程序，隻是角色不同。對于一個并行查詢，其建立 worker 程序的大緻流程如下：

client 建立連接配接， postmaster 為其 fork 一個 backend 程序處理請求
backend 接收使用者請求，并生成并行查詢計劃
執行器向 backgroudworker 注冊 worker 程序（并沒有啟動）
執行器通知（kill） postmaster 啟動 worker 程序
worker 程序與 leader 程序協調執行，将結果傳回 client

動态共享記憶體（Dynamic Shared Memory）與 IPC

PostgreSQL 是多程序架構，程序間通信往往通過共享記憶體和信号量來實作。對于并行查詢而言，執行時建立的 worker 程序與 leader 程序同樣通過共享記憶體實作資料互動。但這部分記憶體無法像普通的共享記憶體那樣在系統啟動時預先配置設定，畢竟直到真正執行時才知道有多少 worker 程序，以及需要配置設定多少記憶體。

PostgreSQL 實作了動态共享記憶體，即在執行時動态建立，用于 leader 與 worker 間通信，執行完成後釋放。基于動态共享記憶體的隊列用于程序間傳遞元組和錯誤消息。

如下圖，每個 worker 在動态共享記憶體中都有對應的元組隊列和錯誤隊列，worker 将執行結果放入隊列中，leader 會從對應隊列擷取元組傳回給上層算子。動态共享記憶體的具體實作原理和細節在此不做展開。

并行執行

以上簡單介紹了并行查詢依賴的兩個重要基礎元件：背景工作程序和動态共享記憶體。前者用于動态建立 worker，以并行執行子查詢計劃；後者用于 leader 和 worker 間通信和資料互動。本節介紹 PostgreSQL 目前支援并行執行的算子的實作原理，包括：

并行掃描，如并行順序掃描，并行索引掃描等
并行連接配接，如并行哈希連接配接，并行 NestLoop 連接配接等
并行 Append

并行掃描

并行掃描的理念很樸素，即啟動多個 worker 并行掃描表中的資料。以前一個程序做所有的事情，無人争搶，也無需配合，如今多個 worker 并行掃描，首先需要解決如何分工的問題。

PostgreSQL 中的并行掃描配置設定政策也很直覺，即

block-by-block

。多個程序間（leader 和 worker）維護一個全局指針

next

，指向下一個需要掃描的 block，一旦某個程序需要擷取一個 block，則通路該指針，擷取 block 并将指針向前移動。

目前支援并行的常用掃描算子有：

SeqScan

，

IndexScan

BitmapHeapScan

以及

IndexOnlyScan

下圖分别是并行

SeqScan

（左）和并行

IndexScan

（右）的原理示意圖，可見兩者均維護一個

next

指針，不同的是

SeqScan

指向下一個需要掃描的 block，而

IndexScan

指向下一個索引葉子節點。

注意，目前并行

IndexScan

僅支援 B-tree 索引。

IndexOnlyScan

的原理類似，隻是無需根據索引頁去查詢資料頁，從索引頁中即可擷取到需要的資料；并行

BitmapHeapScan

同樣維護一個

next

指針，從下層

BitmapIndexScan

節點構成的位圖中依次配置設定需要掃描的 block。

并行連接配接

PostgreSQL 支援三種連接配接算法：

NestLoop

MergeJoin

HashJoin

。其中

NestLoop

和

MergeJoin

僅支援左表并行掃描，右表無法使用并行；PostgreSQL 11 之前

HashJoin

也僅支援左表并行，PostgreSQL 11 支援了真正的并行

HashJoin

，即左右表均可以并行掃描。

以下圖左側的

NestLoop

查詢計劃為例，

NestLoop

左表是并行

Seq Scan

，右表是普通的

Index Scan

，三個程序（1 個 leader，2 個 worker）分别從左表中并行擷取部分資料與右表全量資料做 JOIN。

Gather

算子則将子計劃的結果向上層算子輸出。圖中右表是索引掃描，其效率可能還不錯，如果右表是全表掃描，則每個程序均需要全表掃描右表。

同理，

MergeJoin

也是類似的，左表可以并行掃描，右表不能并行。由于

MergeJoin

要求輸入有序，如果右側計劃需要顯式排序，則每個程序都需要執行

sort

操作，代價較高，效率較低。

PostgreSQL 10 中的并行

HashJoin

如下圖所示，每個子程序都需要掃描右表并建構各自的 HashTable 用于做

HashJoin

PostgreSQL 11 實作了真正的并行

HashJoin

，所有程序并行掃描右表并建構共享的 HashTable，然後各程序并行掃描左表并執行

HashJoin

，避免了 PostgreSQL 10 中各自建構一個私有 HashTable 的開銷。

關于三種 HashJoin 的執行效率以及性能提升，讀者可以參考 THOMAS MUNRO 的這篇

文章

并行 Append

PostgreSQL 中用

Append

算子表示将多個輸入彙聚成一個的操作，往往對應 SQL 文法中的

UNION ALL

。在 PostgreSQL 11 中實作了

partition-wise join

，如果多個分區表的查詢滿足特定連接配接條件（如拆分鍵上的等值連接配接），則可将其轉換為多個子分區的局部 JOIN，然後再将局部 JOIN 的結果

UNION ALL

起來。具體轉換細節以及實作在此不展開，讀者可以參考 Ashutosh Bapat (आशुतोष बापट) 的這篇

。以下給出一個轉換後的示例圖：

在實作并行

Append

之前，

Append

算子下的多個孩子節點均隻能通過一個程序依次執行，并行

Append

則配置設定多個 worker 程序，并發執行多個孩子節點，其孩子節點可以是以上介紹的并行執行算子，也可以是普通的算子。

并行查詢優化

PostgreSQL 實作了基于代價的優化器，大緻流程如下：

考慮執行查詢的可能路徑（Path）
估算代價，并選擇代價最小的路徑
将路徑轉換為計劃供執行器執行

在并行查詢優化中，将路徑節點分為兩類：

parallel-aware 節點，即節點本身可以感覺自己在并行執行的節點，如 Parallel Seq Scan
parallel-oblivious 節點，即節點本身意識不到自己在并行執行，但也可能出現在并行執行的子計劃中（ Gather 以下），如以上提到的并行 NestLoop 計劃中的 NestLoop 節點

并行查詢引入了兩個新的節點：

Gather

GatherMerge

，前者将并行執行子計劃的結果向上層節點輸出，不保證有序，後者能夠保證輸出的順序。

并行查詢優化在生成路徑時，會生成部分路徑（Partial Paths），所謂 partial，即說明該路徑僅處理部分資料，而非全部資料。Partial Paths 從最底層的掃描節點開始，比如

Parallel Seq Scan

，就是一個 partial path；包含 partial path 的路徑（

Gather/GatherMerge

以下）同樣也是 partial path，比如我們在

Partial Seq Scan

節點上做聚合操作（Aggregate），此時的聚合操作是對局部資料的聚合，即

Partial Aggregate

。随後，優化器會在 partial path 之上添加對應的

Gather/GatherMerge

節點，

Gather/GatherMerge

相當于把 partial path 封裝成一個整體，對上屏蔽并行的細節。

并行度

既然要并行執行，就需要解決并行度的問題，即評估需要幾個 worker。目前，PostgreSQL 僅實作了基于規則的并行度計算，大體包括兩部分：

通過 GUC 參數擷取并行度
基于表大小評估并行度

并行度相關的 GUC 參數如下：

max_parallel_workers_per_gather 每個 Gather/GatherMerge 最大的并行 worker 數（不包含 leader）
force_parallel_mode 是否強制使用并行
min_parallel_table_scan_size 使用并行掃描的最小表大小，預設 8MB
min_parallel_index_scan_size 使用并行掃描的最小索引大小，預設 512KB

根據表大小計算并行度的公式如下：

log(x / min_parallel_table_scan_size) / log(3) + 1 workers

以

min_parallel_table_scan_size

為預設值 8MB 來計算，表大小在 [8MB, 24MB) 區間時為 1 個 worker，在 [24MB, 72MB) 區間時為 2 個 worker，以此類推。

需要注意的是，盡管在查詢優化階段已經計算了并行度，但最終執行的時候是否會啟動對應數量的程序還取決于其他的因素，如最大允許的背景工作程序數（

max_worker_processes

），最大允許的并行程序數（

max_parallel_workers

），以及事務隔離級别是否為

serializable

（事務隔離級别可能在查詢優化以後，真正執行之前發生改變）。一旦無法啟動背景工作程序，則由 leader 程序負責運作，即退化為單程序模式。

代價估算

并行查詢優化需要估算

Partial Path

的代價以及新加節點

Gather/GatherMerge

的代價。

Partial Path

對于 Partial Path 中的

parallel-aware

節點，比如

Partial Seq Scan

，由于多個 worker 并行掃描，每個 worker 處理的資料量減少，CPU 消耗也減少，通過如下方法評估

parallel-aware

的 CPU 代價和處理行數。

計算并行除數（parallel_divisor）

double        parallel_divisor = path->parallel_workers;
if (parallel_leader_participation)
{
        double        leader_contribution;
    
        leader_contribution = 1.0 - (0.3 * path->parallel_workers);
        if (leader_contribution > 0)
            parallel_divisor += leader_contribution;
}

以上算法說明，worker 越多，leader 就越少參與執行 `Gather/GatherMerge` 以下的子計劃，一旦 worker 數超過 3 個，則 leader 就完全不執行子計劃。其中 `parallel_leader_participation` 是一個 GUC 參數，使用者可以顯式控制是否需要 leader 參與子計劃的執行。

估算 CPU 代價，即 cpu_run_cost /= parallel_divisor
估算行數， path->rows / parallel_divisor

parallel-oblivious

節點，則無需額外處理，由于其并不感覺自身是否并行，其代價隻需要根據下層節點的輸入評估即可。

Gather/GatherMerge

并行查詢中引入了兩個新的代價值：

parallel_tuple_cost

parallel_setup_cost

parallel_tuple_cost 每個 Tuple 從 worker 傳遞給 master 的代價，即 worker 将一個 tuple 放入共享記憶體隊列，然後 master 從中讀取的代價，預設值為 0.1
parallel_setup_cost 啟動并行查詢 worker 程序的代價，預設值為 1000

在此不具體介紹這兩個節點的代價計算方式，感興趣的讀者可以參考

cost_gather

cost_gather_merge

的實作。

并行限制

PostgreSQL 并行查詢功能日趨完善，但仍然有很多情況不支援使用并行，這也是未來社群需要解決的問題，主要包括以下場景：

任何寫資料或者鎖行的查詢均不支援并行， CREATE TABLE ... AS SELECT INTO ，和 CREATE MATERIALIZED VIEW 等建立新表的指令可以并行
包含 CTE（with...）語句的查詢不支援并行
DECLARE CURSOR 不支援并行
包含 PARALLEL UNSAFE 函數的查詢不支援并行
事務隔離級别為 serializable 時不支援并行

更多的并行限制，讀者可以參考

官網

總結

本文從并行基礎元件、并行執行以及并行查詢優化三方面介紹了 PostgreSQL 的并行查詢特性，每個子產品的介紹都較為宏觀，不涉及太多實作細節。希望讀者可以借此了解 PostgreSQL 并行查詢的全貌，對實作細節感興趣的讀者亦可以此為指引，深入解讀源碼，加深了解。

當然，PostgreSQL 并行特性涉及子產品衆多，實作複雜，筆者對其了解也還有很多不到位的地方，還望大家多多指正。

PostgreSQL 并行查詢概述

舉個例子

并行基礎元件

背景工作程序（Background Worker Process）

動态共享記憶體（Dynamic Shared Memory）與 IPC

并行執行

并行掃描

并行連接配接

并行 Append

并行查詢優化

并行度

代價估算

Partial Path

Gather/GatherMerge

并行限制

總結

參考文獻

繼續閱讀

Sql優化一：sql語句優化

SQL優化SQL語句優化的目的

Nacos 2.0 更新前後性能對比壓測

JAVA高效程式設計指南

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

Storm編譯打包過程中遇到的一些問題及解決方法

關于SQL語言

SQL語言基礎：常用的資料查詢語句

MapReduce的幾個企業級經典面試案例MapReduce的幾個企業級經典面試案例

9.spark Core 進階2--Cashe

淺談企業活動中進行資料分析的重要性

neo4j之cypher使用文檔

Ambari介紹和架構原理

NOSQL安全攻擊

sqlServer根據經緯查距離

win10本地scala和spark安裝安裝scala安裝spark