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

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本家によるガイドも参考になります。

Presto Performance Tuning | Treasure Data
http://docs.treasuredata.com/articles/presto-performance-tuning