ソートキーとラフセットインデックスを使用して、列指向テーブルのクエリを高速化する - AnalyticDB

このトピックでは、ソートキーとラフセットインデックスを使用して列指向テーブルのクエリパフォーマンスを向上させる方法について説明します。

重要

このトピックは、次のインスタンスに適用されます。

カーネルバージョンが20200826以降のリザーブドモードで新しく作成されたインスタンス。
カーネルバージョンが20200906以降のエラスティックモードで新しく作成されたインスタンス。

背景情報

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

並べ替えは、範囲制限付きクエリを高速化します。各列の最小値と最大値はデータベースに保存されます。クエリがWHERE句の範囲を制限する場合、AnalyticDB for PostgreSQLのクエリプロセッサは、テーブルスキャン中に特定の範囲外のブロックをスキップするために最小値と最大値を使用できます。

例えば、テーブルが日付によってソートされた7年間のデータを記憶し、クエリが1ヶ月の日付範囲を指定すると仮定する。この場合、テーブルデータの1/(7 × 12) のみをスキャンすればよく、98.8% のディスクブロックをスキャンから除外することができる。データが日付でソートされていない場合、すべてのディスクブロックをスキャンできます。

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

複合ソート: 制限条件 (where句の制約) がソートキーのプレフィックスサブセットであるか、ソートキーのすべての列で構成されるシナリオに適用されます。このソート方法は、クエリ条件が制限条件の主列を含むシナリオでより便利です。
インターリーブソート: ソートキーの各列に等しい重みを与えます。このソート方法は、クエリ条件が制限条件のサブセットを含むシナリオに対してより有用である。

詳細については、このトピックの「複合ソートとインターリーブソートのパフォーマンス比較」を参照してください。

パフォーマンス比較
- このセクションでは、複合ソートにより、テーブル全体のスキャンと比較して、大まかなセットインデックスのクエリパフォーマンスが向上する例を示します。
  この例では、7年分のデータを格納するTPC-H Lineitemテーブルが使用されます。次に、l_shipdateフィールドを使用するデータと使用しないデータのクエリが比較されます。どちらのクエリも範囲制限されています。
  説明
  TPCのこの実装は、TPCベンチマークから導出され、この実装がTPCベンチマークのすべての要件に準拠していないため、公開されたTPCベンチマークの結果と比較できません。
  テスト手順:
  1. 32ノードインスタンスを作成します。
  2. Lineitemテーブルに130億行を書き込みます。
  3. 1997-09-01から1997-09-30までの時間範囲でデータを照会します。
    - データはl_shipdateでソートされません。
    - データはl_shipdateでソートされます。

テーブルを作成するときにソートキーとして列を定義する

例

create table test(date text, time text, open float, high float, low float, volume int) with(APPENDONLY=true,ORIENTATION=column) ORDER BY (volume);

構文

CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (
[ { column_name data_type  ...} ]
)
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ ORDER BY (column, [ ... ] )]

カーネルのバージョンが20210326より前の場合は、次のステートメントを使用してソートキーを指定します。SORTKEY (column, [ ... ])

テーブルを並べ替える

複合ソートを使用したデータのソート
- ```
SORT [tablename]
```
- カーネルのバージョンが20210326より前の場合は、次のステートメントを使用してソートキーを指定します。
```
VACUUM SORT ONLY [tablename]
```
インターリーブソートを使用したデータのソート
- ```
MULTISORT [tablename]
```
- カーネルのバージョンが20210326より前の場合は、次のステートメントを使用してソートキーを指定します。
```
VACUUM REINDEX [tablename]
```

テーブルに対してSORTまたはMULTISORTステートメントを実行すると、指定されたソートキーに基づいてテーブルがソートされます。ソートされたテーブルに行を追加すると、ソートされていないデータの量が増加し、ラフセットのフィルタリングパフォーマンスが低下する可能性があります。大まかなセットでのフィルタリングのパフォーマンスを確保するには、SORTまたはVACUUM REINDEX (MULTISORT) ステートメントを実行して、テーブルを定期的に並べ替える必要があります。

ソートキーの変更

次のステートメントを実行して、ビジネス要件に基づいて既存の列指向テーブルのソートキーを変更できます。

ALTER [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name SET ORDER BY (column, [ ... ] )

このステートメントはカタログのみを変更し、すぐにはデータをソートしません。データをソートするには、sort table_nameステートメントを実行する必要があります。

例

ALTER TABLE test SET ORDER BY(high,low);

カーネルのバージョンが20210326より前の場合は、次のステートメントを使用してソートキーを変更します。

ALTER TABLE test SET SORTKEY(high,low);

ソートキーを指定してソート方法を選択する

1つ以上の列値を含むSQLクエリ、または日付列などの範囲制限のあるSQLクエリを常に実行する必要がある場合は、それらの列をソートキーとして使用できます。これにより、大まかなセットインデックスに基づいてデータをソートすることで、前述のSQLクエリが高速化されます。

一般的なケースでは、複合ソートを使用することを推奨します。化合物選別は、データに対して追加の分析を行う必要がある。したがって、インターリーブされたテーブルの場合、VACUUM REINDEXはVACUUM SORT ONLYよりも時間がかかります。

特定の列を含むSQLクエリを実行する必要がほとんどない場合は、インターリーブソートを使用してクエリを高速化できます。インターリーブされたソートキーは最大8列を使用できます。

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

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

この例では、同じ4つの列 (id、num1、num2、およびvalue) を持つ2つのテーブルが使用されます。ソートキーとして (id,num1,num2) を指定します。各テーブルには合計1,000万行が含まれています。この例で使用されるテーブルは、AnalyticDB for PostgreSQLの特に大きなテーブルではありませんが、複合ソートとインターリーブソートのパフォーマンスの違いはテーブルで確認できます。パフォーマンスの違いは、大きなテーブルでより大きくなります。

テスト手順:

2つのテーブル (testとtest_multi) を作成し、同じソートキーを設定します。
テストデータをテーブルに書き込みます。
複合ソートを使用してテストテーブルをソートし、インターリーブソートを使用してtest_multiテーブルをソートします。
同じSQLクエリで、複合ソートとインターリーブソートのポイントクエリのパフォーマンスを比較します。
同じSQLクエリで、複合ソートとインターリーブソートの範囲クエリのパフォーマンスを比較します。

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

各テーブルに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;

adbpgadmin=# SELECT count(*) FROM test;
  count
----------
 10000000
(1 row)

adbpgadmin=# SELECT count(*) FROM test_multi;
  count
----------
 10000000
(1 row)

複合ソートとインターリーブソートを使用して2つのテーブルを別々にソートします
- ```
SORT test;
MULTISORT test_multi;
```

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

クエリはプライマリ列でフィルタリングされます。

-- Q1 is filtered on the primary column.
select * from test where id = 100000;
select * from test_multi where id = 100000;

クエリは2番目の列でフィルタリングされます。

-- Q2 is filtered on the second column.
select * from test where num1 = 8766963;
select * from test_multi where num1 = 8766963;

クエリは2番目と3番目の列でフィルタリングされます。

-- Q3 is filtered on the second and third columns.
select * from test where num1 = 100000 and num2=2904114;
select * from test_multi where num1 = 100000 and num2=2904114;

表 1. パフォーマンス比較結果

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

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

クエリはプライマリ列でフィルタリングされます。

-- Q1 is filtered on the primary column.
select count(*) from test where id>5000 and id < 100000;
select count(*) from test_multi where id>5000 and id < 100000;

クエリは2番目の列でフィルタリングされます。

-- Q2 is filtered on the second column.
select count(*) from test where num1 >5000 and num1 <100000;
select count(*) from test_multi where num1 >5000 and num1 <100000;

クエリは2番目と3番目の列でフィルタリングされます。

-- Q3 is filtered on the second and third columns.
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;

表2. パフォーマンス比較結果

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

テスト結論
- - Q1は、ソートキーのプライマリ列を使用してデータをフィルタリングします。この場合、複合ソートは、インターリーブされたソートよりも短いクエリ応答時間を有する。
  - Q2は、ソートキーの非主キー列を使用してデータをフィルタリングします。この場合、複合ソートは効果がなく、インターリーブソートは非常に優れたクエリ性能を有する。
  - Q3は、ソートキーの非主キー列を使用してデータをフィルタリングします。この場合、インターリーブソートは、複合ソートよりも高速で効果的です。インターリーブされたソートキーの列が多いほど、インターリーブされたソートクエリのパフォーマンスが向上します。