PostgreSQL 的 Parallel Aggreation

PostgreSQL 9.6 將會支援 Parallel Aggreation,在多 CPU core 下 aggreation operation 單一 query 的平行化效能改善 (改善非常多):「pgsql: Support parallel aggregation.」:

Parallel workers can now partially aggregate the data and pass the transition values back to the leader, which can combine the partial results to produce the final answer.

在「Parallel Aggregate – Getting the most out of your CPUs」這邊有測試 worker 數量與執行的速度差異:

We performed some tests on a 4 CPU 64 core server with 256GB of RAM using TPC-H @ 100 GB scale on query 1. This query performs some complex aggregation on just over 600 million records and produces 4 output rows.

由於是 64 cores,所以作者測 1 到 64 workers 的效能,這是測試出來的結果:

要注意 Y 軸是對數比例,紅色是理論值,藍色是實際值,可以看出來平行化的效能頗不錯,雖然開到 64 workers 時效率已經不到一半了。

這對離鋒時間的報表運算超好用啊...