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 つのテーブル（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: 先頭列（id）でのフィルター
SELECT * FROM test WHERE id = 100000;
SELECT * FROM test_multi WHERE id = 100000;

-- Q2: 第 2 列（num1）でのフィルター
SELECT * FROM test WHERE num1 = 8766963;
SELECT * FROM test_multi WHERE num1 = 8766963;

-- Q3: 第 2 列および第 3 列（num1, num2）でのフィルター
SELECT * FROM test WHERE num1 = 100000 AND num2 = 2904114;
SELECT * FROM test_multi WHERE num1 = 100000 AND num2 = 2904114;

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

-- Q1: 先頭列（id）での範囲フィルター
SELECT count(*) FROM test WHERE id > 5000 AND id < 100000;
SELECT count(*) FROM test_multi WHERE id > 5000 AND id < 100000;

-- Q2: 第 2 列（num1）での範囲フィルター
SELECT count(*) FROM test WHERE num1 > 5000 AND num1 < 100000;
SELECT count(*) FROM test_multi WHERE num1 > 5000 AND num1 < 100000;

-- Q3: 第 2 列および第 3 列（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 と Q2 の比較）。

本テストでは 1,000 万行を使用していますが、これは AnalyticDB for PostgreSQL にとって比較的小規模なサイズです。実際には、より大規模なテーブルで両手法のパフォーマンス差はさらに顕著になります。