PostgreSQL11preview-平行計算增強彙總

標籤

PostgreSQL , 並行 , 增強 , 11

---

背景

PostgreSQL 11 平行計算能力的增強。

E.1.3.1.2. Parallel Queries

• Allow btree indexes to be built in parallel (Peter Geoghegan, Rushabh Lathia, Heikki Linnakangas)

  支援並行排序，支援並行建立索引(並行寫索引檔案)。

  《PostgreSQL 11 preview – 並行排序、並行索引 (效能線性暴增) 單例項100億TOP-K僅40秒》

• Allow hash joins to be performed in parallel using a shared hash table (Thomas Munro)

  HASH JOIN支援共享雜湊表了。原來是每個parallel worker程式一份雜湊表副本。

• Allow UNION to run each SELECT in parallel if the individual SELECTs cannot be parallelized (Amit Khandekar, Robert Haas, Amul Sul)

  當各個UNION ALL內的子句無法支援並行時，PostgreSQL 11會選擇union的各個子句並行。

  https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0927d2f46ddd4cf7d6bf2cc84b3be923e0aedc52

  query1 union query2 union query3;  
  
  當query1,query2,query3 這些QUERY本身無法並行執行時。  
  
  PostgreSQL 11, 選擇讓 query1,query2,query3 同時執行。  
  
  老版本, 無法並行。  
  

  《PostgreSQL 11 preview – Parallel Append(包括 union all分割槽查詢) (多表平行計算) sharding架構平行計算核心功能之一》

• Allow partition scans to more efficiently use parallel workers (Amit Khandekar, Robert Haas, Amul Sul)

  同上，支援paralle append掃描多個子分割槽。

• Allow LIMIT to be passed to parallel workers (Robert Haas, Tom Lane)

  This allows workers to reduce returned results and use targeted index scans.

  允許LIMIT子句下層到各個paralle worker程式。加速帶LIMIT的並行查詢。

• Allow single-evaluation queries, e.g. WHERE clause aggregate queries, and functions in the target list to be parallelized (Amit Kapila, Robert Haas)

  允許”單次評估的QUERY”並行執行，例如”where子句中的聚合子句”，”select目標中的函式”。

• Add server option parallel_leader_participation to control if the leader executes subplans (Thomas Munro)

  The default is enabled, meaning the leader will execute subplans.

  Allows the leader process to execute the query plan under Gather and Gather Merge nodes instead of waiting for worker processes. The default is on. Setting this value to off reduces the likelihood that workers will become blocked because the leader is not reading tuples fast enough, but requires the leader process to wait for worker processes to start up before the first tuples can be produced. The degree to which the leader can help or hinder performance depends on the plan type, number of workers and query duration.

  允許parallel leader 程式在gather或gather merge節點主動接收worker程式產生的資料，而不是等待。

• Allow parallelization of commands CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW (Haribabu Kommi)

  允許CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW這幾類SQL並行執行。

• Improve performance of sequential scans with many parallel workers (David Rowley)

  並行全表掃描效能增強。

• Add reporting of parallel worker sort activity to EXPLAIN (Robert Haas, Tom Lane)

  explain增加輸出詳情，包括parallel worker節點排序的統計資訊。