Hologres:クラスタリングキーを使用した TopN クエリの高速化

列指向テーブルでは、clustering_key プロパティがデータの物理ソート順を定義します。クラスタリングキーと同じ方向にソートする ORDER BY ... LIMIT N クエリは、数行を読み取っただけで結果を返します。しかし、逆方向にソートするクエリは、フルテーブルスキャンを行ってから結果をソートする必要があります。これは、大規模なテーブルや大きな列を持つテーブルでは高コストになります。

リバーススキャンを使用すると、スキャンオペレーターはクラスタリングキーの物理的な逆順でデータを読み取ります。これにより、逆順の ORDER BY ... LIMIT N クエリは、フォワードクエリと同様に少量のデータのみを読み取るようになり、フルテーブルスキャンとソートオペレーターは不要になります。

背景情報

制限事項

• 列指向テーブル (orientation = 'column') のみをサポートします。ソート列は、テーブルのクラスタリングキーまたはそのプレフィックスである必要があります。

• ORDER BY ... LIMIT N 形式のクエリのみを高速化します。

• この機能は、クラスタリングキーの逆順でソートするクエリを対象としています。同じ順序でソートするクエリは、この機能がなくても既にクラスタリングキーの恩恵を受けています。

• この機能は、テーブル定義における clustering_key のセマンティクスやストレージ形式を変更するものではありません。

クエリでリバーススキャンを使用できる条件

この機能を有効にすると、オプティマイザーは、クエリのソート方向と NULL の位置の両方が物理レイアウトと一致する場合にのみ、リバーススキャンを使用します。Hologres のストレージでは、NULL 値は常に物理的な末尾に配置されます (NULLS LAST)。以下の表にルールを示します。

要するに、NOT NULL 列の場合、どちらの方向のデフォルトの ORDER BY も、SQL を変更することなく高速化できます。NULL 値許容列の場合、クエリ内の NULL の位置が物理レイアウトと一致する必要があります。詳細については、「テーブル設計の推奨事項」および「V5.0.0 へのアップグレード後の動作変更」をご参照ください。

リバーススキャンの有効化

hg_enable_reverse_scan_for_clustering_keys GUC パラメーターを使用して、この機能のオン/オフを切り替えます。

この機能は、以下のいずれかのレベルで有効にできます。

• セッションレベル： 現在の接続にのみ適用されます。この機能を試したり、単一のクエリを検証したりするために使用します。

  -- 有効化
  SET hg_enable_reverse_scan_for_clustering_keys = on;
  -- 無効化 (元の動作に戻す)
  SET hg_enable_reverse_scan_for_clustering_keys = off;

• データベースレベル： 永続化され、データベースへのすべての新しい接続にデフォルトで適用されます。データベース全体でこの機能を有効にするために使用します。以下のステートメントを実行するには、スーパーユーザーまたはデータベースオーナーである必要があります。

  -- 有効化 (既存の接続には影響しません。適用するには再接続してください)
  ALTER DATABASE <DB_NAME> SET hg_enable_reverse_scan_for_clustering_keys = on;
  -- 無効化
  ALTER DATABASE <DB_NAME> SET hg_enable_reverse_scan_for_clustering_keys = off;
  -- データベースレベルの設定を削除し、デフォルト値 (off) に戻す
  ALTER DATABASE <DB_NAME> RESET hg_enable_reverse_scan_for_clustering_keys;

クエリがリバーススキャンを使用していることの確認

EXPLAIN ANALYZE を実行して実行計画を表示します。重要な指標は、Seq Scan ノードに Order By: xxx という注釈があるかどうかです：

• 注釈あり：クエリは高速化されます。スキャンオペレーターはソート済みの行を返し、追加の Sort ノードは不要です。

• 注釈なし：クエリは高速化されません。スキャンの上に余分なソートが実行されます。

また、ストレージから読み取られた行数である scan_rows を比較することもできます。クエリが高速化されると、この値はテーブルの総行数よりもはるかに小さくなり、高速化されない場合はテーブルの総行数に近くなります。

次の例では、「例」の demo_single_notnull テーブルを使用します。このテーブルには clustering_key='ts' があり、すべての列は NOT NULL です。次の逆順クエリを実行します。

EXPLAIN ANALYZE SELECT * FROM demo_single_notnull ORDER BY ts DESC LIMIT 1000;

• 機能が有効な場合 (高速化済み) ：Seq Scan ノードには Order By: ts という注釈があり、内部に Sort ノードはありません。

  ->  Seq Scan on demo_single_notnull
        Order By: ts                 ← 高速化済み: スキャンがソートされた順序で行を返します
      [scan_rows=21760]              ← 少数の行グループのみがスキャンされます

• 機能が無効な場合 (高速化なし) ：Seq Scan ノードには Order By 注釈がなく、その上に Sort ノードがあり、scan_rows はテーブルの総行数と等しくなります。

  ->  Sort
        Sort Key: ts
        ->  Seq Scan on demo_single_notnull
            [scan_rows=1000000]      ← フルテーブルスキャン

テーブル設計の推奨事項

方向固有の SQL や NULLS 句なしで、両方向のクエリを確実に高速化するには、以下の推奨事項に従ってください：

• クラスタリングキーのソート方向を指定しない (推奨)： 'ts:desc' や 'ts:asc' ではなく、clustering_key = 'ts' を指定します。方向が指定されていない場合、asc が使用されます。hg_enable_reverse_scan_for_clustering_keys を有効にすると、ORDER BY ts ASC LIMIT N と ORDER BY ts DESC LIMIT N の両方が高速化されるため、アプリケーションは方向を気にする必要がありません。これは、asc の物理レイアウトが PostgreSQL のデフォルトの NULL 順序 (ASC NULLS LAST と DESC NULLS FIRST) と一致し、どちらの方向のデフォルトの ORDER BY も高速化されるためです。

• 可能な限りクラスタリングキー列を NOT NULL として宣言する： 一致させるべき NULL の位置がないため、デフォルトの ORDER BY は、SQL を変更することなく、どのクエリ方向でも、どのクラスタリングキー方向でも高速化されます。

• NULL 値許容列と desc 方向の組み合わせを避ける： この組み合わせでは、PostgreSQL のデフォルトの NULL の位置がストレージレイアウトと逆になります。クエリが高速化されるのは、NULLS LAST (フォワード) または NULLS FIRST (リバース) を明示的に追加した場合のみであり、これは忘れがちです。テーブルが既にこの設計を使用している場合は、SQL に一致する NULLS 句を追加してください。

V5.0.0 へのアップグレード後の動作変更

Hologres V5.0.0 では、NULL 値許容のクラスタリングキー列に関する正確性の問題が修正されています。以前のバージョンでは、オプティマイザーはクラスタリングキーを使用するかどうかを決定する際に、ソート方向のみを考慮し、NULL の順序を無視していました。その結果、NULL 値許容列の NULL 値を持つ行が結果から欠落する可能性がありました。V5.0.0 以降、NULL の順序はデフォルトで無視されなくなりました。クエリが高速化されるのは、その NULL の順序がストレージレイアウトと一致する場合のみです。

影響： Hologres V5.0.0 にアップグレードした後、NULL 値許容のクラスタリングキー列に対する一部のクエリは、NULL の位置がストレージレイアウトと一致しない場合、クラスタリングキーを使用しなくなる可能性があります。たとえば、desc 列に対するクエリで NULLS LAST を省略したり、NULLS FIRST を明示的に指定したりする場合です。これらのクエリは、フルテーブルスキャンとそれに続くソートにフォールバックし、時間がかかるようになる場合があります。これは意図された正確性の修正であり、バグではありません。

NULL の順序を無視する以前の動作に戻すには、次の GUC パラメーターを使用します。

-- NULL の順序を無視する以前の動作に戻す
SET hg_ignore_clustering_key_null_order = on;

例

-- 1) クラスタリングキーを持つ列指向テーブルを作成する
CREATE TABLE demo_single_notnull (
    id      int  NOT NULL,
    ts      int  NOT NULL,
    payload text NOT NULL
) WITH (orientation='column', clustering_key='ts');

-- 2) リバーススキャンを有効にする
SET hg_enable_reverse_scan_for_clustering_keys = on;

-- 3) 逆順クエリを実行する：クラスタリングキーは ts であり、クエリは ts 値が最も大きい 1,000 行を返す
SELECT * FROM demo_single_notnull ORDER BY ts DESC LIMIT 1000;

-- 4) クエリが高速化されていることを確認する
EXPLAIN ANALYZE SELECT * FROM demo_single_notnull ORDER BY ts DESC LIMIT 1000;
-- Seq Scan ノードに "Order By: ts" という注釈があり、scan_rows がテーブルの総行数よりもはるかに小さい場合、クエリは高速化されています。

関連ドキュメント

• クラスタリングキー

• GUC パラメーター

• EXPLAIN と EXPLAIN ANALYZE