PostgreSQL 11 preview - 并行計算 增強 彙總

标簽

PostgreSQL , 并行 , 增強 , 11

https://github.com/digoal/blog/blob/master/201805/20180519_02.md#%E8%83%8C%E6%99%AF 背景

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

https://github.com/digoal/blog/blob/master/201805/20180519_02.md#e1312-parallel-queries 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節點排序的統計資訊。