最初の条件に一致する行はごく少数である一方、2つ目の条件はテーブルのほぼ全体に一致するため、PostgreSQLはid < 11ではインデックスを使い、id > 11ではシーケンシャルスキャンを行うことがあります。どちらの演算子もB-treeインデックスに対応しており、プランナーは推定コストを比較します。

このガイドでは、Macを使う開発者が合成した記事行でこの選択を再現し、実行計画を読み、その違いに注意を払う必要があるかを確認する方法を説明します。取得した現行のPostgreSQL 18ドキュメントとnode-postgresの現行ドキュメントに沿っています。実行計画の詳細を比較する前に、サーバーのバージョンを確認してください。例は、バージョンやマシンが違っても同じ実行計画になることを保証するものではありません。PostgreSQL: Using EXPLAIN

同じインデックスでスキャン方法が異なるのはなぜですか？

B-treeインデックスは、より小さい値とより大きい値の両方との比較に対応しています。比較の方向が変わっても、インデックスが使えなくなるわけではありません。変わるのは値の範囲、つまりプランナーが取得すると予測する行数です。PostgreSQL: Index Types

連続した整数IDが1から100,000まである、記事行をちょうど100,000件含む例示用テーブルを考えてみましょう。

条件 一致する行数 テーブルに占める割合
id < 11 10 0.01%
id > 11 99,989 99.989%
id = 11 1 0.001%

この件数は合成データから算出したもので、既存データベースを測定した結果ではありません。どちらの範囲にもID 11は含まれません。最初の範囲に一致する10個のIDは、インデックスで効率よく見つけられます。2つ目の範囲では、どのみちほぼすべての行が必要です。

通常のPostgreSQLインデックススキャンでは、インデックスからエントリを取得し、対応する行をテーブルの主ストレージであるヒープから読み込みます。ヒープ行は多数のページに分散している場合があります。ほぼすべてのページが必要なときは、インデックスをたどって参照先の行を取得するより、テーブルを直接読み込むほうが低コストになることがあります。PostgreSQL: Index-Only Scans and Covering Indexes

したがって、数値の11にプランナー上の特別な意味はありません。データが違えば、大なり条件が狭い範囲になることもあります。PostgreSQLは、テーブル統計を使って一致する行の割合（選択性）を推定します。境界値の数値の大きさだけでは、その割合は決まりません。PostgreSQL: Statistics Used by the Planner

Macでこの比較を再現するにはどうすればよいですか？

既存のPostgreSQL接続と、MacのTerminalで使えるpsqlコマンドが必要です。インストール手順はこの演習の対象外です。普段使っているデータベース接続をpsqlで開いてください。接続設定を受け取り、対話型のSQLプロンプトを提供します。次のブロックを新しいセッションで続けて実行します。PostgreSQL: psql

セットアップでは、合成した記事テキストだけを含む一時テーブルを作成します。一時テーブルはセッション終了時に削除されます。通常の整数主キーによって一意なB-treeインデックスが自動作成されるため、idに別途インデックスを作る必要はありません。PostgreSQL: CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

PostgreSQLの整数型generate_seriesは、デフォルトの刻み幅が1で、両端を含みます。そのため、件数を数えるクエリでは、合計100,000行、11未満が10行、11より大きい値が99,989行と返るはずです。PostgreSQL: Set Returning Functions

行を読み込んだ後にANALYZEを実行して、プランナーがデータ投入済みテーブルの統計情報を使えるようにします。このコマンドは統計情報を収集します。EXPLAIN ANALYZEの実行時間計測オプションとは目的が異なります。PostgreSQL: ANALYZE

次に、全行を返す2つのクエリを比較します。

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

これらのコマンドは各クエリを実行し、実行計画と実際の行数を報告しますが、選択された記事行すべては返しません。BUFFERSはブロックのアクティビティを追加し、TIMING OFFは各ノードの時間計測を無効にします。実行状況の調査には役立ちますが、該当する記事データすべてをデスクトップアプリケーションへ送る時間は測定しません。PostgreSQL: EXPLAIN

通常の設定では、狭い範囲に対してインデックスベースの実行計画、広い範囲に対してシーケンシャルスキャンが選ばれると予想されます。ただし、これはあくまで予想です。PostgreSQLは推定に基づいて、ビットマップ実行計画やほかの該当する実行計画を選ぶ場合もあります。この例のSQLは確認済みですが、準備時に実行できるPostgreSQLサーバーがなかったため、実際には実行していません。行数の計算は別途確認しました。

実行計画のどこを見れば選択理由がわかりますか？

スキャンノードと、その推定rows、実際のrowsを確認します。最初のクエリでは、Index Condを伴うIndex Scan、またはBitmap Heap Scanの下にあるBitmap Index Scanを探します。2つ目のクエリでは、Filterを伴うSeq Scanを探します。ビットマップ実行計画もインデックスを使用します。ヒープページを取得する前に、一致する行の位置を整理します。PostgreSQL: Using EXPLAIN

続いて、次の点を確認します。

この例での観察事項 調べること
推定値が実際の10行と99,989行におおむね一致する 一致件数の違いが、選択された方法の違いを説明しています。
推定行数が実際の行数と大きく異なる プランナーの設定を変える前に統計情報を更新します。
かなり小さいサンプルで、どちらの条件もシーケンシャルスキャンになる テーブルが小さいため、直接スキャンするほうが低コストな可能性があります。
ビットマップ実行計画が現れる 別のインデックスベースの方法として、行数の推定とヒープ処理を比較します。

プランナーのコストは任意単位の推定値であり、ミリ秒ではありません。コスト100を所要時間として解釈せず、PostgreSQLのモデルの一部として比較してください。例では一時テーブルを使っているため、バッファのアクティビティはローカルブロックに表示されます。通常のテーブルとインデックスのバッファは共有ブロックに表示されます。PostgreSQL: EXPLAIN

推定値に疑問がある場合は、同じセッションで次の診断クエリを実行します。

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

ヒストグラムは、範囲の推定に使われる値の分布を示します。相関は、列の論理上の順序と物理的な行の順序がどれほど近いかを示します。どちらかの極端に近い値の場合、ヒープへのインデックスアクセスのコストが低く見積もられることがあります。どちらの値も、PostgreSQLがスキャン方法を切り替えるべき普遍的な割合を示すものではありません。PostgreSQL: pg_stats

node-postgres経由でクエリを比較するにはどうすればよいですか？

node-postgresはクエリ文字列とパラメーター値をPostgreSQLに送り、そこで実行計画が作成されます。クエリAPIでは、SQLの後に値の配列を指定できるため、どちらの比較でも同じ境界値11を使えます。node-postgres: Queries

Node.jsで独立して再現するには、動作するNode.js/npm環境と、既存のPostgreSQL接続設定を使います。新しいプロジェクトディレクトリでnpm init -y、続けてnpm install pgを実行します。以下のプログラム全体をcompare-scans.mjsとして保存し、node compare-scans.mjsを実行します。クライアントはPGHOST、PGPORT、PGDATABASEなどのPostgreSQL環境設定を使います。既存のローカルデータベース用の設定を使用してください。node-postgres: Connecting

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

このプログラムは独自のサンプルテーブルを作成し、すべての操作を1つの接続で行います。両方の実行計画を表示した後、接続を閉じます。JavaScriptの構文はNode.jsで確認しましたが、準備時にデータベース上では実行していません。接続のセットアップに失敗した場合は、スキャン動作を解釈する前に問題を解決してください。

名前付きプリペアドステートメントで比較結果は変わりますか？

node-postgresのクエリ設定にnameを加えると、接続ごとに名前付きプリペアドステートメントが作成されます。これにより、PostgreSQLがパラメーター値に基づくカスタムプラン、または複数回の実行で共有される汎用プランを使うという違いが生じます。node-postgres: Queries、PostgreSQL: PREPARE

PostgreSQL 18のデフォルトの自動方針では、最初の5回はカスタムプランが使われます。その後、PostgreSQLは汎用プランの推定コストとカスタムプランの平均コストを比較します。6回目の実行で自動的に汎用プランへ切り替わるわけではありません。PostgreSQL: PREPARE

アプリケーションの名前付きステートメントの動作がリテラルSQLによるテストと異なる場合は、同じ接続でEXPLAIN EXECUTEを使ってそのステートメントを調べてください。汎用プランではパラメーター記号が表示され、カスタムプランでは渡された値に置き換わります。2つの比較演算子はSQLテキストが異なるため、別々の名前付きステートメントにしてください。PostgreSQL: PREPARE

クエリやプランナーの設定を変更すべきなのはどんなときですか？

まず、クエリが実際に行う処理を保ったまま比較してください。SELECT *をSELECT idに置き換えると、PostgreSQLが取得するデータが変わります。必要な列がすべて適切なインデックスに含まれていれば、インデックスオンリースキャンが可能になります。ヒープアクセスを避けられるかどうかは可視性マップにも左右されるため、インデックスオンリープランでもヒープへのアクセスがゼロになるとは限りません。PostgreSQL: Index-Only Scans and Covering Indexes

一致する記事をすべて取得する処理なら、その要件を維持したまま実行計画を比較します。順序付けされた少数の結果だけが必要なら、実際のORDER BYとLIMITを使うクエリを試してください。並べ替えと早期終了によって、望ましい実行計画が変わることがあります。PostgreSQL: Using EXPLAIN

統計情報を確認した後、既存のSQLセッションで別の実行計画を比較できます。

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

この設定はシーケンシャルスキャンを選びにくくしますが、完全に抑止することはできません。結果は診断用の比較として使ってください。PostgreSQLはseq_page_costとrandom_page_costによって、シーケンシャルアクセスと非シーケンシャルアクセスのページコストもモデル化しています。それらの相対値を変えると実行計画の選択が変わることがありますが、ドキュメントでは、少数の実験ではなくクエリのワークロード全体でコスト設定を評価するよう勧めています。PostgreSQL: Query Planning

この合成テーブルで決め手になる比較は、一致する行が10件か99,989件かという点です。正確なクエリを確認し、統計情報を更新して、推定行数と実際の行数を比較し、必要に応じてプリペアドプランの動作を調べてください。これらの手順で、シーケンシャルスキャンが要求された処理に合っているかを確かめ、さらに変更する場合の根拠を得られます。

出典