AnalyticDB:ソートの最適化

背景情報

テーブルを作成するときに、1つ以上の列をソートキーとして指定できます。 テーブルにデータが書き込まれた後、ソートキーでテーブルデータをソートできます。

ソートは範囲クエリを高速化します。 ソート後、範囲クエリを高速化するために、各列の最大値と最小値が記録されます。 クエリで範囲条件が使用されている場合、AnalyticDB for PostgreSQLのクエリエンジンは、最大値と最小値を使用して、テーブルスキャン中にディスクブロックを特定の範囲外にスキップできます。

たとえば、テーブルに日付でソートされた7年間のデータが格納されている場合、特定の月のデータをクエリする場合、スキャンする必要があるのはテーブルデータの1/(7 × 12) のみで、スキャン中に98.8% のディスクブロックをスキップできます。 データが日付でソートされていない場合、すべてのディスクブロックをスキャンできます。

AnalyticDB for PostgreSQLは、2つのソート方法をサポートしています。

• 複合ソート: フィルタ条件がソートキーのプレフィックスサブセットであるシナリオに適用されます。 たとえば、このメソッドは、SQLクエリのフィルタ条件にソートキーの最初の列が含まれている場合に使用できます。

• インターリーブソート: ソートキーの各列に等しい重みを割り当てます。 この方法は、クエリ条件がフィルタ条件サブセットを含むシナリオでより便利です。

ソートキーを選択

• SQLクエリの等価条件または範囲条件で特定の列が頻繁に使用される場合は、これらの列をソートキーとして使用できます。 これにより、データソートと大まかなセットインデックスを使用してSQLクエリを高速化できます。 ほとんどの場合、複合ソートを使用することをお勧めします。

• SQLクエリのフィルター条件で異なる列が使用されている場合は、インターリーブソートを使用してクエリを高速化できます。 ほとんどの場合、インターリーブソートは、複合ソートよりも時間がかかります。 これは、インターリーブソートがデータに対して実行される余分な分析を必要とするためです。

  説明

  インターリーブされたソートキーには、最大8列を含めることができます。

• JOIN条件として特定の列を頻繁に使用する場合は、JOIN列を配布キーとソートキーの両方として指定できます。 これにより、ハッシュ結合の代わりにマージ結合を選択できます。 結合キーに基づいてデータがすでにソートされているため、マージ結合のソートフェーズはスキップできます。

複合ソートとインターリーブソートのパフォーマンス比較

このセクションでは、同じデータを含む2つのテーブルが、複合ソートとインターリーブソートを使用して別々にソートされます。 テーブルに対するクエリの結果は、2つのソート方法が異なるシナリオで異なるパフォーマンスを提供することを示しています。

1. 次のステートメントを実行して、id、num1、num2、およびvalue列を持つ2つのテーブルを作成し、id、num1、およびnum2列を2つのテーブルのソートキーとして指定します。

   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. 次のステートメントを実行して、2つのテーブルに10万行のデータを挿入します。

   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;

   次のステートメントを実行して、2つのテーブルの合計行数を照会します。

   • SELECT count(*) FROM test;

     次の情報が返されます。

       count
     ----------
      10000000
     (1 row)

   • SELECT count(*) FROM test_multi;

     次の情報が返されます。

       count
     ----------
      10000000
     (1 row)

3. 複合ソートとインターリーブソートを使用して、2つのテーブルを別々にソートします。

   次のステートメントを実行して、複合ソートを使用してテストテーブルをソートします。

   SORT test;

   次のステートメントを実行して、インターリーブソートを使用してtest_multiテーブルをソートします。

   MULTISORT test_multi;

4. 平等クエリのパフォーマンスを比較します。

   • Q1は、ソートキーの最初の列に基づいてフィルタリングされます。

     SELECT * FROM test WHERE id = 100000;
     SELECT * FROM test_multi WHERE id = 100000;

   • Q2は、ソートキーの2番目の列に基づいてフィルタリングされます。

     SELECT * FROM test WHERE num1 = 8766963;
     SELECT * FROM test_multi WHERE num1 = 8766963;

   • Q3は、ソートキーの2番目と3番目の列に基づいてフィルタリングされます。

     SELECT * FROM test WHERE num1 = 100000 AND num2=2904114;
     SELECT * FROM test_multi WHERE num1 = 100000 AND num2=2904114;

   次の表に、パフォーマンスの比較結果を示します。

   ソート方法	Q1	Q2	Q3
   化合物ソート	0.026s	3.95s	4.21s
   インターリーブソート	0.55s	0.42s	0.071s

5. 範囲クエリのパフォーマンスを比較します。

   • Q1は、ソートキーの最初の列に基づいてフィルタリングされます。

     SELECT count(*) FROM test WHERE id>5000 AND id < 100000;
     SELECT count(*) FROM test_multi WHERE id>5000 AND id < 100000;

   • Q2は、ソートキーの2番目の列に基づいてフィルタリングされます。

     SELECT count(*) FROM test WHERE num1 >5000 AND num1 <100000;
     SELECT count(*) FROM test_multi WHERE num1 >5000 AND num1 <100000;

   • Q3は、ソートキーの2番目と3番目の列に基づいてフィルタリングされます。

     SELECT count(*) FROM test WHERE num1 >5000 AND num1 <100000 AND num2 < 100000;
     SELECT count(*) FROM test_multi WHERE num1 >5000 AND num1 <100000 AND num2 < 100000;

   次の表に、パフォーマンスの比較結果を示します。

   ソート方法	Q1	Q2	Q3
   化合物ソート	0.07s	3.35s	3.64s
   インターリーブソート	0.44s	0.28s	0.047s

テストの結論:

• Q1はソートキーの最初の列を使用してデータをフィルタリングします。 この場合、複合ソートを使用するクエリは、インターリーブソートを使用するクエリよりも応答時間が短くなります。

• Q2は、ソートキーの最初ではない列を使用してデータをフィルタリングします。 この場合、インターリーブソートを使用するクエリは、複合ソートを使用するクエリよりも優れています。

• Q3は、ソートキーの非最初の列を使用してデータをフィルタリングします。 この場合、インターリーブソートを使用するクエリは、複合ソートを使用するクエリよりも高速で効果的です。 インターリーブソートキーの列が多いほど、インターリーブソートを使用するクエリのパフォーマンスが向上します。

ソート加速

並べ替えアクセラレーション機能がサポートされています。 SORT <tablename> ステートメントを実行すると、指定されたテーブル内のデータがソートされます。 次に、AnalyticDB for PostgreSQLは、SORTなどの演算子をストレージ層にプッシュし、データの物理的な順序に基づいてクエリを高速化します。 基になるデータが順序付けられると、クエリを高速化できます。 この機能により、ソートキーに基づいてSORT、AGG、またはJOIN演算子を含むクエリを高速化できます。

この例では、farという名前のテストテーブルを使用して、並べ替えの加速前後のクエリ時間を比較します。

1. 次のステートメントを実行して、farという名前のテストテーブルを作成します。

   CREATE TABLE far(a int,  b int)
   WITH (APPENDONLY=TRUE, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5)
   DISTRIBUTED BY (a)  --Distribution key
   ORDER BY (a);       --Sort key

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オプティマイザをオフにします。