Edited at

分散SQLエンジン“Presto”のクエリチューニング

More than 1 year has passed since last update.

Prestoを利用し始めましたので、クエリのチューニング話も行いたいと思います。


SELECT句のチューニング

SELECT * FROM ...は大変遅いので、データがあるかを確認する用途ならSELECT COUNT(1) FROM ...としましょう。

それでも中のデータを色々見たい場合には、カラムをなるべく限定して記述しましょう。

もちろん、SELECT *として取れる同じ数のカラムを指定したら同様に遅くなります。

特にvarchar型の数十文字以上の文字列が入るカラムは必要の無い限り外すべきです。格段に速くなります。

もしカラム名が分からないのであれば、WHERE句を指定せずに一瞬で結果が返ってくる次のようなクエリで確認すると速いです。

例)SELECT * FROM ... LIMIT 1;


JOINのチューニング

Prestoにはcost-based JOIN optimizationsが実装されていないため、

あんまり行数の多いテーブルをJOINしてしまうと、オンメモリで処理できずに失敗(OutOfMemory)します。


行数の多いテーブルを先に書く

そんな時は行数の多いテーブルをFROMの先頭にして、行数の少ないテーブルをJOIN句で繋げていくと良いです。

SELECT

*
FROM
very_huge_table -- 大きいテーブルを先にする
INNER JOIN
small_table ON
very_huge_table.some_id = small_table.id


WITH句を用いる

しかしながら、それでもメモリオーバーすることもあります。

そんな時はWITH句を用いることで処理を分割すると、上手く行くこともあります。

これは「共通テーブル式」や「CTE (Common Table Expression)」とも呼ばれます。

-- MySQL感覚で書いたクエリ

SELECT
transactions.id AS transaction_id,
devices.id AS device_id,
devices.name AS device_name,
maker.name AS maker_name
FROM
devices
INNER JOIN transactions ON devices.id = transactions.device_id AND transactions.request_type = 13
INNER JOIN maker ON maker.id = devices.maker_id
;

-- WITH句を使った例
WITH reduced_transactions AS (
SELECT
id AS transaction_id,
device_id
FROM
transactions
WHERE
request_type = 13
)
SELECT
reduced_transactions.transaction_id,
devices.id AS device_id,
devices.name AS device_name,
maker.name AS maker_name
FROM
reduced_transactions
INNER JOIN devices ON devices.id = reduced_transactions.device_id
INNER JOIN maker ON maker.id = devices.maker_id


substr()を用いる

その他、文字列でJOINするが、先頭数文字だけで区別できるのであれば、substr()を用いるのも手ですね。

-- deviceがPC,MB,SPの3種類なら先頭1文字で判定できます

SELECT
*
FROM
foo
INNER JOIN
bar
ON
substr(foo.device, 1, 1) = substr(bar.device, 1, 1)


WHERE句のチューニング

カーディナリティの高い(条件を大きく絞り込める)条件を先に書きましょう。

また、条件句は数が少なければ少ない方が良いです。

条件を減らしても結果が変わらないようなものであれば、削れる物を削りましょう。

その他、パーティショニングに使っているキーを指定できるならば、行った方が良いです。

例えばTreasureDataならtimeキーでパーティショニングしていますので、指定期間内のみを対象とすると速くなります。


型変換の技

TreasureDataが提供するPrestoには、smart_digest()というUDFがあります。

これは、文字列を短いハッシュ値に変える関数ですが、まだドキュメントに記載されていないそうです。

数値型ではなく文字列型でのJOINを行う際にはメモリ量をかなり削減できるため有用です。

利用方法としては、smart_digest(column)とします。次の解説も参考になります。

Hint: GROUP BY, JOIN and PARTITION BY consume large amount of memory especially if keys are large.

Please try to shorten the key size using substr() or smart_digest() functions. For example:

GROUP BY : SELECT min(key) AS key FROM rows GROUP BY smart_digest(key)
JOIN : SELECT t1.key FROM t1 JOIN t2 ON smart_digest(t1.key) = smart_digest(t2.key)
PARTITION BY : SELECT row_number() OVER (PARTITION BY smart_digest(key) ORDER BY time) FROM rows


まとめ

他にも便利なクエリチューニングテクニックを見つけましたら、編集リクエストを頂けると幸いです。

また、TreasureData本家によるガイドも参考になります。