本投稿は、
PostgreSQL Advent Calendar 2016 の 12/16 担当です。
昨日は「
.NET Core で PostgreSQL を使ってみたら結構いけていたという話」を
@creativewebjp さんが書いてくださいました。
■パラレルクエリと戯れて
さて、Ver9.6の登場までポスグレ界の話題を総ざらいしていた
パラレルクエリですが、みなさん使ってますか？
大量データの領域で真価を発揮するものあって、なかなか現実的なサイズで試せていないのが現状で、採用に二の足を踏んでしまう、なんて話もちらほら聞いています。私は仕事で
EnterpriseDB社が開発するPostgreSQLの商用版、
EDB Postgresを担当していますが、そのβテストを兼ねてOSS版PostgreSQLのパラレル動作をみていたのですが、やはり試してみてわかることが結構ありました。
採用するかどうか迷っている方がいたら、とっかかりは非常に簡単なので、是非トライして、自身のアプリケーションでどの程度使えるかを評価してみるべきだと思います。バッチリとハマったときの効果は抜群ですよ。
とは言え、良いときの性能評価はそこそこ出回っていると思うので、今回は、案外うまくいかないパラレルクエリの話を紹介します。
■いろいろ検証して得られた「間違うケース」
間違うケース1 でかい表同士をJOINするとき
前提知識として、PostgreSQLのパラレル・クエリは、表のサイズで並列度が決定されます。
適当な例で言うなら、10GBの表は8並列、500MBの表は4並列ぐらいで動きます。
んまあ、これはあくまでも単一の表をFROM句に書いてSELECTした場合に、これぐらいの並列度が良さそうと判断されたにすぎないです。
10GBを
1プロセスで読むコスト > 8プロセスで分担して、後でGather（集約）するコスト
と判断したので、そういう実行計画が選択されるということですね。
これが、JOINの場合はどうやら変わってくるようです。
2つの表をパラレルで読んで、Gatherするとなると、 そのオーバーヘッドの方が大きくなってしまって、せっかくパラレルにしたのに所要時間はむしろ長くなってしまいます。
どちらもパラレルが効くぐらい大きい表なので、適切にパラレル処理して欲しいところではありますが、単純に表のサイズだけでは決められず、どちらか一方だけパラレルに動いてくれた方が良い結果になるのです。もちろんそういう目論見で、プランナはどちらか一方の表をパラレルで読もうとします。
問題は、PostgreSQLだと「どちらの表をパラレルで処理するか？」の調整が非常に難しいこと。約10GBのlineoder表と、約500MBのpart表をごく単純に結合してみると、このような実行計画になりました。
/* 実行したクエリ（ごく単純なJOIN） */
EXPLAIN ANALYZE
SELECT lo_partkey,sum(lo_quantity)
FROM lineorder
JOIN part
ON lo_partkey = p_partkey
WHERE p_name LIKE 'k%'
GROUP BY lo_partkey;
/* いかにもパラレルが効きそうなlineorderが普通にSeqScanされてる */
-> Hash Join (cost=28661.34..3682122.56 rows=1111820 width=9)
(actual time=67.061..57496.965 rows=1286337 loops=1)
-> Seq Scan on lineorder (cost=0.00..3214594.56 rows=114066256 width=9)
★(actual time=0.010..27750.208 rows=114066226 loops=1)
-> Hash (cost=28490.76..28490.76 rows=13646 width=4)
(actual time=66.904..66.904 rows=15731 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 682kB
-> Gather (cost=1000.00..28490.76 rows=13646 width=4)
(actual time=0.342..61.640 rows=15731 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Parallel Seq Scan on part (cost=0.00..26126.16 rows=4402 width=4)
★(actual time=0.029..59.519 rows=3933 loops=4)
Filter: ((p_name)::text ~~ 'k%'::text)
Rows Removed by Filter: 346067
今回のケースでは、どうしても小さい方の表をパラレルにしたがる（先に読むからだろうか？）で、大きい方の表をシーケンシャルに読んでしまいました。
効果は未確認ですが、解決策としては、ALTER TABLEでそのテーブルを読むときに使うワーカーの数を指定できるので、それでpartテーブルの方は0に固定してしまうことでしょうか。
ただし、「こっちクエリではpart表をパラレルに読んでほしいんだよね～」という要望に応えるのが難しくなります。
そこでEDB Postgresです。EDB Postgresでは、オプティマイザ・ヒントを指定でき、2017年1月にリリース予定のEDB 9.6ではパラレル・ヒントに対応します。そこで実行計画をいじくるとこんな感じです。
/* EDB版の実行したクエリ（パラレル・ヒントで各表の並列度を指定） */
EXPLAIN ANALYZE
SELECT /*+ PARALLEL(part 0) PARALLEL(lineorder 4) */ lo_partkey,sum(lo_quantity)
FROM lineorder
JOIN part
ON lo_partkey = p_partkey
WHERE p_name LIKE 'k%'
GROUP BY lo_partkey;
/* スキャンの並列度が変わり、Hash Joinまでパラレル実行している */
HashAggregate (cost=2624706.98..2636997.29 rows=983225 width=36)
(actual time=17414.085..17421.061 rows=11272 loops=1)
Group Key: lineorder.lo_partkey
-> Gather (cost=39151.57..2619147.88 rows=1111820 width=9)
(actual time=181.809..16260.314 rows=1286337 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=38151.57..2506965.88 rows=1111820 width=9)
☆(actual time=233.851..16886.113 rows=257267 loops=5)
Hash Cond: (lineorder.lo_partkey = part.p_partkey)
-> Parallel Seq Scan on lineorder (cost=0.00..2359097.64 rows=28516564 width=9)
★(actual time=0.034..8187.008 rows=22813245 loops=5)
-> Hash (cost=37981.00..37981.00 rows=13646 width=4)
(actual time=233.525..233.525 rows=15731 loops=5)
Buckets: 16384 Batches: 1 Memory Usage: 682kB
-> Seq Scan on part (cost=0.00..37981.00 rows=13646 width=4)
★(actual time=0.026..225.321 rows=15731 loops=5)
狙った表をパラレルスキャンさせることで、PostgreSQLで生じていた問題をバッチリ解決することができました。
間違うケース2 Window関数なのか、ソートなのか。原因不明（ヲイ！）
PostgreSQLでは、Windows関数つかって、それでないと1クエリで得ることがなかなか難しい複雑な集計を任せることができます。
とは言えその複雑さはなかなかなもので、さぞかしプランナは頭を悩ませることでしょう。
以前に本ブログで書いた「
PostgreSQLらしい集計クエリ」で紹介しているテーブルで、データを何重にもいれまくって、2GBぐらいの表を用意しました。それを集計してみます。
/* window関数 */
EXPLAIN ANALYZE
SELECT *,
rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
SELECT
sales_date::date as "売上日"
,shop as "ブランド"
,sum(price) as "売上"
FROM tab1
GROUP BY "売上日","ブランド") AS "集計";
/* 結果イメージ。ブランド毎に売上の多かった日ランキング */
売上日 | ブランド | 売上 | 順位
------------+------------+-----------+------
2016-05-11 | DIESEL | 632141504 | 1
2016-07-31 | DIESEL | 622096896 | 2
2016-11-29 | DIESEL | 620818688 | 3
：
2016-04-06 | Dior | 962435003 | 1
2016-03-23 | Dior | 889015872 | 2
：
/* この時の実行計画がこちら。パラレルになってる。約9秒。 */
WindowAgg (cost=3430456.39..3454473.19 rows=1200840 width=52) (actual time=9776.175..9777.199 rows=1095 loops=1)
-> Sort (cost=3430456.39..3433458.49 rows=1200840 width=44) (actual time=9776.165..9776.300 rows=1095 loops=1)
Sort Key: "集計"."ブランド", "集計"."売上" DESC
Sort Method: quicksort Memory: 134kB
-> Subquery Scan on "集計" (cost=3115228.89..3235312.89 rows=1200840 width=44)
(actual time=9764.055..9773.283 rows=1095 loops=1)
-> Finalize GroupAggregate (cost=3115228.89..3223304.49 rows=1200840 width=44)
(actual time=9764.054..9773.062 rows=1095 loops=1)
Group Key: ((tab1.sales_date)::date), tab1.shop
-> Sort (cost=3115228.89..3133241.49 rows=7205040 width=44)
(actual time=9764.012..9765.509 rows=7665 loops=1)
Sort Key: ((tab1.sales_date)::date), tab1.shop
Sort Method: quicksort Memory: 1270kB
-> Gather (cost=1064650.75..1851272.12 rows=7205040 width=44)
(actual time=6412.121..9757.054 rows=7665 loops=1)
Workers Planned: 6
Workers Launched: 6
-> Partial GroupAggregate (cost=1063650.75..1129768.12 rows=1200840 width=44)
(actual time=6518.572..9655.286 rows=1095 loops=7)
Group Key: ((tab1.sales_date)::date), tab1.shop
-> Sort (cost=1063650.75..1075676.95 rows=4810477 width=19)
(actual time=6516.858..8298.955 rows=4123429 loops=7)
Sort Key: ((tab1.sales_date)::date), tab1.shop
☆Sort Method: external merge Disk: 125680kB
-> Parallel Seq Scan on tab1 (cost=0.00..332432.96 rows=4810477 width=19)
★(actual time=0.071..1195.131 rows=4123429loops=7)
お、パラレルになった。
しかし、これ、Disk Sortになっていますね。メモリソートになればもっと早くなるんじゃ。SET work_mem to '2GB' などとして再実行
あれ？
/* 実行計画がこちら。メモリソートになったらなぜか非パラレルに。約20秒かかっている。 */
WindowAgg (cost=1000838.70..1024855.50 rows=1200840 width=52) (actual time=21083.366..21084.609 rows=1095 loops=1)
-> Sort (cost=1000838.70..1003840.80 rows=1200840 width=44) (actual time=21083.347..21083.521 rows=1095 loops=1)
Sort Key: "集計"."ブランド", "集計"."売上" DESC
Sort Method: quicksort Memory: 134kB
-> Subquery Scan on "集計" (cost=849559.20..879580.20 rows=1200840 width=44)
(actual time=21075.482..21080.224 rows=1095 loops=1)
-> HashAggregate (cost=849559.20..867571.80 rows=1200840 width=44)
(actual time=21075.480..21080.024 rows=1095 loops=1)
Group Key: (tab1.sales_date)::date, tab1.shop
-> Seq Scan on tab1 (cost=0.00..633087.75 rows=28862860 width=19)
★(actual time=0.064..7888.289 rows=28864000 loops=1)
Planning time: 0.234 ms
Execution time: 21097.712 ms
なんと、ソートが軽量に済むやいなや、パラレル動くことをやめちゃいました。なんてこった。ディスクソートしていたときのほうが圧倒的に早いので、このケースこそプランナが間違えてるとしか思えません。
しかもこれ、上記のJOINのケースのように
パラレルさせないという選択ではなく、
パラレルして欲しい方向なので、これ以上PostgreSQLでは調整のやりようがないです。困った。
そんなときはEDB Postgres
ヒントを使って・・・・
EXPLAIN ANALYZE
SELECT /*+ PARALLEL(tab1 6) */ *,
rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
SELECT
sales_date::date as "売上日"
,shop as "ブランド"
,sum(price) as "売上"
FROM tab1
GROUP BY "売上日","ブランド") AS "集計";
あれ。
悩むこと数十分。サブクエリの側にヒントを入れて、無事解決しました。
/* EDBでヒントをいれてみる。正解はこの位置 */
EXPLAIN ANALYZE
SELECT *,
rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
SELECT /*+ PARALLEL(tab1 6) */
sales_date::date as "売上日"
,shop as "ブランド"
,sum(price) as "売上"
FROM tab1
GROUP BY "売上日","ブランド") AS "集計":
/* EDBでヒントを使うと、パラレルスキャンし、メモリソートもできた */
WindowAgg (cost=1327297.76..1351662.32 rows=1218228 width=56) (actual time=9667.291..10095.048 rows=441334 loops=1)
-> Sort (cost=1327297.76..1330343.33 rows=1218228 width=48) (actual time=9667.270..9746.242 rows=441334 loops=1)
Sort Key: "集計"."ブランド", "集計"."売上" DESC
Sort Method: quicksort Memory: 46768kB
-> Subquery Scan on "集計" (cost=1176747.00..1204157.13 rows=1218228 width=48) (actual time=8986.449..9216.731 rows=441334 loops=1)
-> Finalize HashAggregate (cost=1176747.00..1191974.85 rows=1218228 width=48) (actual time=8986.447..9129.055 rows=441334 loops=1)
Group Key: tab1.sales_date, tab1.shop
-> Gather (cost=357488.67..1103653.32 rows=7309368 width=48) (actual time=3751.793..5775.296 rows=3081355 loops=1)
Workers Planned: 6
Workers Launched: 6
-> Partial HashAggregate (cost=356488.67..371716.52 rows=1218228 width=48) (actual time=3736.607..4506.717 rows=440194 loops=7)
Group Key: tab1.sales_date, tab1.shop
-> Parallel Seq Scan on tab1 (cost=0.00..320408.67 rows=4810667 width=23) (actual time=0.041..870.314 rows=4123429 loops=7)
ぶっちゃけactual timeを見ると元々9秒だったのが10秒と、むしろ若干悪いのだが・・・ディスクソートを排除したいという欲望を満たすべく試したらこうなった、と言う話。たぶんデータ量とかでこの辺はセンシティブに最適解は変わってしまうのだと思います。
間違・・・わないケース3 グループ集計（GROUPING SETS）
結論から言うと、いろいろ頑張ったけど思ったより早くならないのでプランナが正しかった！説があります。以下のクエリを実行してみると、PostgreSQLで一向にパラレルになってくれません。
/* グループ集計のクエリ */
EXPLAIN ANALYZE
SELECT
shop AS "ブランド"
,kind AS "種類"
,count(*) AS "数量"
FROM tab1
GROUP BY GROUPING SETS (("ブランド"),("種類"),());
/* 結果のイメージ */
ブランド | 種類 | 数量
------------+------------+----------
DIESEL | | 8448000
Paul Smith | | 15488000
dior | | 4928000
| | 28864000
| シャツ | 15488000
| ジャケット | 4224000
| パンツ | 9152000
/* PostgreSQLでの実行計画 パラレルにならない */
GroupAggregate (cost=4137591.06..8147200.27 rows=14 width=26)
(actual time=27350.631..48641.465 rows=7 loops=1)
Group Key: shop
Group Key: ()
Sort Key: kind
Group Key: kind
-> Sort (cost=4137591.06..4209751.06 rows=28864000 width=18)
(actual time=22549.349..26069.469 rows=28864000 loops=1)
Sort Key: shop
Sort Method: quicksort Memory: 2953433kB
-> Seq Scan on tab1 (cost=0.00..560942.00 rows=28864000 width=18)
(actual time=0.025..5461.728 rows=28864000 loops=1)
Planning time: 0.160 ms
Execution time: 49108.929 ms
EDBでヒントをいれてみます。
GroupAggregate (cost=6784457.73..10794066.94 rows=14 width=26)
(actual time=45959.909..70152.520 rows=7 loops=1)
Group Key: shop
Group Key: ()
Sort Key: kind
Group Key: kind
-> Sort (cost=6784457.73..6856617.73 rows=28864000 width=18)
(actual time=39595.620..44537.665 rows=28864000 loops=1)
Sort Key: shop
Sort Method: quicksort Memory: 2953433kB
-> Gather (cost=1000.00..3207808.67 rows=28864000 width=18)
(actual time=0.442..18630.270 rows=28864000 loops=1)
Workers Planned: 6
Workers Launched: 6
-> Parallel Seq Scan on tab1 (cost=0.00..320408.67 rows=4810667 width=18)
(actual time=0.052..1111.439 rows=4123429 loops=7)
Planning time: 0.130 ms
Execution time: 70680.047 ms
パラレル度が上がったが早くならないケースもある。ということで。
■総評
パラレルクエリで効果を得づらいケースを3パターン紹介しました。
これはたまたま私が検証する中で気付いたもので、他にもあるかもしれないし、賢い解決策があるのかもしれません。
本投稿では触れていませんが、同じ検証の中で、1クエリ実行するのに
50分かかっていた処理が、パラレルクエリとテーブル・パーティショニングの組合せで8秒まで短縮していますから、ハマれば効果は抜群です。
しかしながら、間違うケースもこうやってポンポン出てきたということは、これから採用を考えている人は、自身のアプリケーションでどの程度性能がでるか、どんな書き方をすれば効果がでるか、（そしてEDBにすればどう変わるか。ぐへへ！）是非試してみて欲しいと思います。
策をいろいろ考えるのはパズルみたいで楽しかったです。
明日は
ぬこ@横浜さんが書いてくれます。楽しみです！
