AnalyticDB:ソートキーとラフインデックスを使用した列指向テーブルのクエリ高速化

ソートメソッドの選択

クエリパターンに適したメソッドを選択するには、以下のガイドラインを使用してください。

• ほとんどのクエリがソートキーの先頭列でフィルターする場合は、複合ソートを使用します。先頭列の述語に対して最速の応答時間を生成します。複合ソートによる再ソートは、追加のデータ分析を実行するため、インターリーブソートよりも時間がかかることに注意してください。

• クエリが先頭列以外の列、またはソートキーの任意のサブセットでフィルターする場合は、インターリーブソートを使用します。インターリーブソートキーは最大 8 列までサポートします。クエリが参照するソートキー列が多いほど、パフォーマンス上のメリットは大きくなります。

• どちらを使用すればよいかわからない場合は、複合ソートから始めてください。これはよりシンプルな選択肢であり、クエリに明確な先頭フィルター列がある場合に最高のパフォーマンスを発揮します。

TPC-H ベンチマーク：範囲クエリに対するソートキーの影響

このセクションでは、7 年分のデータを格納する TPC-H の Lineitem テーブルを使用して、複合ソートがフルテーブルスキャンと比較してラフセットインデックスのクエリパフォーマンスをどのように向上させるかを示します。

この TPC の実装は TPC ベンチマークから派生したものであり、TPC ベンチマークのすべての要件に準拠しているわけではないため、公開されている TPC ベンチマークの結果とは比較できません。

テスト設定：

1. 32 ノードのインスタンスを作成します。

2. Lineitem テーブルに 130 億行を書き込みます。

3. 1997-09-01 から 1997-09-30 までの期間のデータをクエリし、データが l_shipdate でソートされている場合とソートされていない場合の結果を比較します。

複合ソート vs. インターリーブソート：クエリ形状によるパフォーマンスの違い

次の例では、同一のデータとソートキーを持つ 2 つのテーブルを使用して、2 つのメソッドが異なるクエリ形状でどのようにパフォーマンスを発揮するかを示します。

テスト設定：

• 2 つのテーブル (test と test_multi)、それぞれに 4 つの列：id、num1、num2、value

• ソートキー：両方のテーブルで (id, num1, num2)

• テーブルごとに 1,000 万行

• 複合ソートでソートされた test (SORT test)

• インターリーブソートでソートされた test_multi (MULTISORT test_multi)

テーブルの作成とデータの挿入：

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);

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;

SORT test;
MULTISORT test_multi;

ポイントクエリのパフォーマンス

3 つのクエリはすべてソートキー列でフィルターしますが、位置が異なります。

-- Q1: filter on the leading column (id)
SELECT * FROM test WHERE id = 100000;
SELECT * FROM test_multi WHERE id = 100000;

-- Q2: filter on the second column (num1)
SELECT * FROM test WHERE num1 = 8766963;
SELECT * FROM test_multi WHERE num1 = 8766963;

-- Q3: filter on the second and third columns (num1, num2)
SELECT * FROM test WHERE num1 = 100000 AND num2 = 2904114;
SELECT * FROM test_multi WHERE num1 = 100000 AND num2 = 2904114;

範囲クエリのパフォーマンス

-- Q1: range filter on the leading column (id)
SELECT count(*) FROM test WHERE id > 5000 AND id < 100000;
SELECT count(*) FROM test_multi WHERE id > 5000 AND id < 100000;

-- Q2: range filter on the second column (num1)
SELECT count(*) FROM test WHERE num1 > 5000 AND num1 < 100000;
SELECT count(*) FROM test_multi WHERE num1 > 5000 AND num1 < 100000;

-- Q3: range filter on the second and third columns (num1, num2)
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 の結果は、クエリが先頭列をバイパスする場合、インターリーブソートが複合ソートを大幅に上回るパフォーマンスを発揮することを示しています。

• インターリーブソートは列数に応じてスケールします。クエリが参照するソートキー列が多いほど、インターリーブソートのパフォーマンス上の利点は大きくなります (Q3 vs. Q2)。

このテストでは 1,000 万行を使用していますが、これは AnalyticDB for PostgreSQL にとっては中程度のサイズです。2 つのメソッド間のパフォーマンスの違いは、より大きなテーブルでより顕著になります。