AnalyticDB:ソートの最適化

セットアップ

1. 両方のテーブルを作成します：

   CREATE TABLE test(id int, num1 int, num2 int, value varchar)
   WITH (APPENDONLY=TRUE, ORIENTATION=column)
   DISTRIBUTED BY (id)
   ORDER BY (id, num1, num2);
   
   CREATE TABLE test_multi(id int, num1 int, num2 int, value varchar)
   WITH (APPENDONLY=TRUE, ORIENTATION=column)
   DISTRIBUTED BY (id)
   ORDER BY (id, num1, num2);

2. 各テーブルに 1,000 万行を挿入します：

   INSERT INTO test(id, num1, num2, value)
   SELECT g,
     (random() * 10000000)::int,
     (random() * 10000000)::int,
     (ARRAY['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'child', 'phone'])[floor(random() * 10 + 1)]
   FROM generate_series(1, 10000000) AS g;
   
   INSERT INTO test_multi SELECT * FROM test;

3. 対応するメソッドを使用して各テーブルをソートします：

   SORT test;            -- 複合ソート
   MULTISORT test_multi; -- インターリーブソート

等価クエリの結果

3 つのクエリが、ソートキー列の異なる組み合わせでフィルターをかけます：

• Q1 — 先頭列のみ： WHERE id = 100000

• Q2 — 2 番目の列のみ： WHERE num1 = 8766963

• Q3 — 2 番目と 3 番目の列： WHERE num1 = 100000 AND num2 = 2904114

範囲クエリの結果

同じ列の組み合わせで、範囲述語を使用します：

• Q1 — 先頭列： WHERE id > 5000 AND id < 100000

• Q2 — 2 番目の列： WHERE num1 > 5000 AND num1 < 100000

• Q3 — 2 番目と 3 番目の列： WHERE num1 > 5000 AND num1 < 100000 AND num2 < 100000

結論

• Q1 (先頭列フィルター)： 複合ソートの方が高速です。フィルターがソートキーの先頭列と一致する場合、クエリエンジンはインターリーブソートが必要とする追加の分析なしで効率的にブロックをスキップします。

• Q2 (先頭以外の列フィルター)： インターリーブソートは、プレフィックスだけでなく各列に独立した最小/最大範囲を割り当てるため、複合ソートよりもパフォーマンスが優れています。

• Q3 (複数の先頭以外の列)： インターリーブソートが大幅に高速です。フィルターに含まれる先頭以外の列が多いほど、その利点は大きくなります。各追加列がブロックスキャンの範囲を狭めるのに貢献するためです。

セットアップ

1. far テーブルを作成します：

   CREATE TABLE far(a int, b int)
   WITH (APPENDONLY=TRUE, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5)
   DISTRIBUTED BY (a)  -- 分散キー
   ORDER BY (a);       -- ソートキー

2. 100 万行を挿入します：

   INSERT INTO far VALUES (generate_series(0, 1000000), 1);

3. テーブルをソートします：

   SORT far;

クエリパフォーマンスの比較

ORDER BY

• ソートアクセラレーション適用前：

• ソートアクセラレーション適用後：

GROUP BY

• ソートアクセラレーション適用前：

• ソートアクセラレーション適用後：

JOIN

• ソートアクセラレーション適用前：

• ソートアクセラレーション適用後：

  説明

  JOIN オペレーターでソートアクセラレーションを使用するには、ORCA オプティマイザーを無効にし、マージ結合アルゴリズムを有効にします：

  SET enable_mergejoin TO on;
  SET optimizer TO off;

  

まとめ