Postgres EXPLAIN：シーケンシャルスキャンは常にパフォーマンスのボトルネックなのか？- 徹底解説

はじめに

リレーショナルデータベースの世界では、パフォーマンス最適化は絶え間ない追求です。開発者やデータベース管理者は、ボトルネックを特定し、効率を向上させるために、クエリ実行計画を頻繁に調査します。PostgreSQLでEXPLAINが表示するさまざまな操作の中でも、「シーケンシャルスキャン」（Sequential Scan）は、しばしば非効率的な「フルテーブルスキャン」と見なされ、何としても排除すべきだと直感的に懸念されます。しかし、この広範な仮定は、重要なニュアンスを見落としています。シーケンシャルスキャンは常に悪い兆候なのでしょうか？それは、インデックスを必要とするパフォーマンスキラーに必然的に繋がるのでしょうか？この記事では、PostgreSQLのEXPLAIN出力の複雑さを、特にシーケンシャルスキャンに焦点を当てて掘り下げ、この単純な見方に異議を唱え、クエリ実行におけるその役割について、よりバランスの取れた理解を提供します。

シーケンシャルスキャンの性質

シーケンシャルスキャンの「悪さ」を分析する前に、PostgreSQLにおけるクエリ計画と実行のコアコンセプトについて共通の理解を確立しましょう。

主要な用語

• EXPLAIN: SQLステートメントの実行計画を表示するPostgreSQLコマンド。データベースシステムがクエリをどのように実行するか、実行する操作、およびそれらを実行する順序を示します。
• シーケンシャルスキャン（Seq Scan）: PostgreSQLがテーブルのすべての行を最初から最後まで読み取るデータベース操作。「フルテーブルスキャン」とも呼ばれます。
• インデックススキャン（Index Scan）: PostgreSQLがインデックスを使用してテーブル内の特定の行を特定するデータベース操作。テーブル全体を読むのではなく、インデックスをナビゲートしてデータを迅速に見つけます。
• コスト（Cost）: EXPLAIN出力における操作の相対的な費用見積もり。時間の単位ではなく、ディスクI/O、CPU使用率、メモリアクセスなどの要因に基づく無次元の値です。通常、コストが低いほど、実行が速くなります。
• 行数（Rows）: 操作によって返される推定行数。
• 幅（Width）: 操作によって返される行の推定平均幅（バイト単位）。
• バッファ（Buffers）: （EXPLAIN (ANALYZE, BUFFERS)で表示）実行中にヒット/読み取り/ダーティ化された共有およびローカルバッファの数を示し、I/Oアクティビティを指示します。

シーケンシャルスキャンの仕組み

PostgreSQLにおけるシーケンシャルスキャンの核心は、ストレージからデータブロックを順番に読み取り、テーブル全体（または特定のクエリに関連する部分）が処理されるまで続きます。データベースシステムは、各行がWHERE句で指定された条件を満たしているかどうかを検査します。行が条件を満たした場合、結果セットに含まれるか、クエリプランの次の操作に渡されます。

シーケンシャルスキャンがそれほど悪くない場合：コンテキストの理解

シーケンシャルスキャンに対する一般的な反射的な反応は、すぐにインデックスを作成することです。しかし、PostgreSQLのクエリオプティマイザは洗練されています。特定の状況下では、シーケンシャルスキャンが最も効率的な戦略となり得ることを理解しています。

実用的な例を考えてみましょう。数百万行を持つ大きなproductsテーブルがあるとします。

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- サンプルデータの挿入（数百万行）
INSERT INTO products (name, description, price, category)
SELECT
    'Product ' || generate_series,
    'Description for product ' || generate_series,
    (random() * 1000)::numeric(10, 2),
    CASE (generate_series % 5)
        WHEN 0 THEN 'Electronics'
        WHEN 1 THEN 'Books'
        WHEN 2 THEN 'Clothing'
        WHEN 3 THEN 'Home Goods'
        ELSE 'Food'
    END
FROM generate_series(1, 5000000); -- 500万行

次に、EXPLAINを使用していくつかのクエリを分析しましょう。

シナリオ 1: 大量の行を取得する場合

クエリがテーブルの大部分を取得する必要がある場合、シーケンシャルスキャンはインデックススキャンよりも速くなることがあります。なぜなら？インデックススキャ​​ンには2つのステップが含まれます。まず、インデックスを走査して行ポインタ（TID）を見つけ、次に、それらのポインタを使用してテーブルから実際の行データを取得します。テーブル内をあちこちジャンプするこの「ランダムI/O」は、多くの行が必要な場合、テーブルを連続して読み取るよりもコストが高くなる可能性があります。

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE price > 10;

productsの価格が10より大きいものが大幅な割合を占める場合（サンプルのランダムな価格が1000までのことを考えると、これは非常に可能性が高いです）、PostgreSQLはシーケンシャルスキャンを選択するかもしれません。EXPLAIN出力は次のようになります。

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on products  (cost=0.00..109375.00 rows=4999999 width=472) (actual time=0.046..1237.498 rows=4999999 loops=1)
   Filter: (price > '10'::numeric)
   Rows Removed by Filter: 0
   Buffers: shared hit=43750 read=0 dirtied=0 written=0
 Planning Time: 0.160 ms
 Execution Time: 1251.234 ms

ここでは、オプティマイザは、ほぼすべての行が条件を満たすと正しく見積もっています。この場合、priceにインデックスを作成すると、インデックスの使用（インデックスページの読み取り、次にランダムなデータページの取得）のオーバーヘッドが、行をスキップするメリットを上回るため、クエリは実際には遅くなるでしょう。

シナリオ 2: 小さなテーブル

非常に小さなテーブルの場合、インデックスを読み取って走査するオーバーヘッドは、単純なシーケンシャルスキャンのコストを超える可能性があります。クエリオプティマイザはこれを認識するほど賢いです。

CREATE TABLE small_table (
    id SERIAL PRIMARY KEY,
    data TEXT
);
INSERT INTO small_table (data) SELECT 'Some data ' || generate_series FROM generate_series(1, 100);

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM small_table WHERE id = 50;

idは主キー（したがってインデックス付き）ですが、インデックス構造を調べるよりもテーブル全体を読み取る方が速いと判断した場合、オプティマイザは非常に小さなテーブルに対してシーケンシャルスキャンを選択する可能性があります。ただし、PRIMARY KEYの単一の正確な一致については、通常Index Scanが優先されますが、この原則は、小さなテーブルでインデックスが作成されていない列のフィルターに一般的に適用されます。

シナリオ 3: データの局所性とキャッシュ効率

シーケンシャルスキャンは、最新のハードウェアアーキテクチャ、特にCPUキャッシュとディスクプリフェッチからしばしば恩恵を受けます。データが連続して読み取られると、ディスク上の連続したブロックにある可能性が高く、オペレーティングシステムとストレージデバイスがデータをプリフェッチできるようになります。これにより、非常に高速なデータ転送が可能になります。データが頻繁にアクセスされる場合、オペレーティングシステムのファイルシステムキャッシュ、またはデータベースの共有バッファに既に存在している可能性があり、「ディスク読み取り」は事実上「メモリ読み取り」になります。

シナリオ 4: 有用なインデックスの欠如

クエリのWHERE句を効率的に満たすことができるインデックスが存在しない場合、シーケンシャルスキャンは唯一の選択肢です。そのような場合、インデックスはパフォーマンスを向上させる可能性がありますが、シーケンシャルスキャン自体は「悪い」のではなく、単に必要なフォールバックです。

EXPLAIN (ANALYZE, BUFFERS)
SELECT name, price FROM products WHERE description ILIKE '%amazing%';

フルテキスト検索インデックス（これは別の種類のインデックスです）がない限り、ILIKEを使用して長いdescriptionフィールド内のテキストを検索することは、B-treeインデックスが任意のテキスト内のパターンマッチング用に設計されていないため、ほぼ間違いなくシーケンシャルスキャンにつながります。

シーケンシャルスキャンを心配すべき場合

常に悪いわけではありませんが、シーケンシャルスキャンは、特に以下のような状況では、最適化の機会を逃した兆候であることがよくあります。

1. 大きなテーブルから少数の行をフィルタリングする場合: クエリがエンタープライズの99.9％の行をフィルタリングし、大きなテーブルから数行しか返さない場合、インデックスがほぼ確実に正しい選択です。数行を見つけるために数百万行を読むコストは法外です。
2. インデックスなしのOrder By / Group By: クエリに、インデックスが作成されていない列に対するORDER BYまたはGROUP BY句があり、クエリがそれ以外の場合はシーケンシャルスキャンを実行している場合、PostgreSQLはメモリまたはディスク（「filesort」）で結果セット全体をソートする必要があるかもしれません。これは、大規模なデータセットでは非常にコストがかかります。インデックスは、この追加ステップを回避するために、事前にソートされたデータを提供できます。
3. 大規模テーブルでの高I/O: EXPLAIN (ANALYZE, BUFFERS)で、少数の行を返す大きなテーブルでのシーケンシャルスキャンに対する高いreadバッファ数を示す場合、大量のデータが不必要にディスクから取得されていることを示します。これはインデックス化の主要な候補です。

結論

しばしば単純に「悪い」とラベル付けされるシーケンシャルスキャンは、実際にはPostgreSQLで非常に効果的であり、時には避けられない操作です。重要なのはコンテキストです。テーブルデータの大部分を取得するクエリ、小さなテーブル、またはキャッシュ効率が高い場合、シーケンシャルスキャンはインデックススキャンよりも優れたパフォーマンスを発揮する可能性があります。ただし、巨大なテーブルのごく一部をフィルタリングする場合、特にORDER BYまたはGROUP BYを使用する場合、または過剰なディスクI/Oが発生している場合、シーケンシャルスキャンはインデックス最適化の機会を示す可能性が高いです。EXPLAINと基盤となるデータ分布に関する知識のある理解は、情報に基づいたパフォーマン​​スチューニングの決定を行うために不可欠です。シーケンシャルスキャンを盲目的に恐れるのではなく、その目的を理解し、真に重要な場所を最適化してください。