実行時間を繰り返し測定し、ルートノードのバッファ数と組み合わせてPostgreSQLのプランを比較します。このMacのターミナルを使った手順では、架空の記事行を使ってインデックスがデータベースの作業量を減らすかを検証し、1回の実行結果を誤って解釈しかねないキャッシュの影響も考慮します。

何を一緒に測定すべきですか？

今回の作業では、インデックス追加の前後で同じ記事一覧クエリを比較します。選択する列、カテゴリ、並び順、上限件数、データは変更しません。経過した実行時間と、結果を生成するために必要なバッファの動作の両方を記録します。

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)を使用します。ANALYZEオプションはクエリを実行し、TIMING OFFは各ノードの計時を無効にしつつ、文全体の実行時間は保持します。機械可読な出力を使うと、測定を繰り返して収集しやすくなります。PostgreSQL: EXPLAIN

次の測定値を合わせて読みます。

測定値 意味
Execution Time 計画時間を除くサーバーの実行時間
Planning Time プランの生成と最適化にかかった時間
Shared Hit Blocks PostgreSQLの共有キャッシュから満たされたバッファアクセス
Shared Read Blocks 共有バッファに取り込まれたブロック
Shared hitsとreadsの合計 共有バッファへのアクセス量を比較する際に役立つ値
Temp Read BlocksとTemp Written Blocks ソートがメモリを超えて一時ファイルに書き出される場合などの、一時作業ファイルの動作

共有ブロックは通常のテーブルとインデックスに属します。ローカルブロックは一時テーブルとインデックスに属し、一時作業ファイルのブロックは別の区分です。親ノードには子ノードのバッファ動作が含まれるため、実行全体の合計にはルートノードを使い、作業箇所を特定するには子ノードを調べます。すべてのノードの数値を合計してはいけません。PostgreSQL: EXPLAIN

バッファ数は重複を除いた数ではありません。同じブロックに再アクセスすると、再度カウントされる場合があります。また、プランナーのコストは任意の単位で、ミリ秒ではありません。実際の実行時間と実際のバッファ数を比較し、プランナーの選択を理解するために推定コストを参照します。PostgreSQL: Using EXPLAIN

共有読み取りがあっても、Macのストレージデバイスが物理読み取りを行った証拠にはなりません。PostgreSQLのI/O統計では、ストレージから取得したデータと、すでにオペレーティングシステムのページキャッシュにあったデータとを区別できません。ストレージに関する別の証拠がない限り、測定値は共有バッファ読み取りとして説明してください。PostgreSQL: The Cumulative Statistics System

Macで実験を準備し、バージョンの違いを確認する

Terminalで接続できる、稼働中のローカルPostgreSQLデータベースとnpmが利用可能なNode.jsから始めます。この手順はPostgreSQL 18を対象とし、Node.js 22または24を使用します。node-postgresのドキュメントでは、これらのNode.jsバージョンとの互換性が記載され、npm install pgによるインストール方法が説明されています。node-postgres

比較コマンドはPostgreSQL 16と17のドキュメントにも記載されています。PostgreSQL 18ではEXPLAIN ANALYZEにバッファ出力が自動的に含まれますが、BUFFERSを明記すれば、これらのバージョンを通じて意図が明確になります。新しい任意設定が古いサーバーにもあるとは想定しないでください。PostgreSQL 16: EXPLAIN、PostgreSQL 17: EXPLAIN、PostgreSQL 18リリースノート

新しい作業ディレクトリで、ドライバーをインストールします。

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

普段使っているPostgreSQL接続から開ける、既存のローカル練習用データベースを使います。このスクリプトはnew Client()を使用します。これはpsqlと同じ接続環境変数を読み取ります。たとえば、データベース名がplan_labなら、PGDATABASE=plan_labを指定してスクリプトを実行します。必要に応じて、既存のPGHOSTまたはPGPORTの設定を追加してください。node-postgres: Connecting

このプログラムはトランザクション内に10万件の架空の記事を作成し、2つのスキーマ状態を比較した後、トランザクションをロールバックします。スキャンが共有バッファに表示されるよう、通常のテーブルを使います。PostgreSQLのドキュメントによると、ROLLBACKはトランザクションによる更新を破棄します。デモ用テーブル名がすでに存在する場合、置き換えずに作成が失敗します。PostgreSQL: ROLLBACK

実行前後を完全に比較する

以下の完全なプログラムをcompare-plans.mjsとして保存します。入力データを自動生成し、各フェーズで記録対象外のウォームアップを1回行い、測定した7回分を保持して、各フェーズの最初の測定プランを出力します。行の生成には、PostgreSQLのドキュメントに記載されたgenerate_series関数を使います。PostgreSQL: Set Returning Functions

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

同じディレクトリから、練習用データベースの実際の名前に置き換えて実行します。

PGDATABASE=plan_lab node compare-plans.mjs

このプログラムは、client.query(text, values)を通じてSQLとパラメーター値を別々に送ります。返された列名QUERY PLANからプランを読み取ります。node-postgresは結果列をオブジェクトのプロパティに対応付け、PostgreSQLのJSONをJavaScriptオブジェクトに変換します。node-postgres: Queries、node-postgres: pg.Result、node-postgres: Data Types

単独のANALYZEコマンドは、プランナー向けにテーブル統計を収集します。これは、EXPLAIN内の実行計測用オプションANALYZEとは異なります。ここでは架空の行を読み込んだ後に統計を収集するため、どちらのフェーズもテーブルに関する同じ初期情報から始められます。PostgreSQL: ANALYZE

JavaScriptによる中央値とバッファ合計の計算は、決定論的な合成入力で確認済みです。調査時の環境ではPostgreSQLが利用できなかったため、データベース実験全体は準備中に実行していません。この記事には測定済みのベンチマーク結果はありません。

2つのプランでは何が変わるはずですか？

候補となるインデックスでは、等価条件で絞り込むカテゴリを先頭に置き、その後に要求された並べ替え列を続けます。PostgreSQLのドキュメントによると、B-treeの先頭列に対する等価条件は、スキャンするインデックス範囲を絞ります。そのため、このクエリには理にかなった候補インデックスです。PostgreSQL: Multicolumn Indexes

インデックス作成前にはスキャンとソートがあり、その後はLIMITの下に順序付きインデックススキャンが現れるか確認します。これは出力で確認する予想であり、必ずその形になると約束するものではありません。条件に合うB-treeインデックスがあれば、順序付きの行を直接提供できます。また、並べ替えとLIMITを組み合わせることで、残りの一致行を処理せずに済む場合があります。PostgreSQL: Indexes and ORDER BY

出力されたプランでは、次の3点を確認します。

  1. どちらのフェーズでもルートが50行を返し、結果の不一致がないとプログラムが報告していることを確認します。
  2. スキャンとソートを担当するノードを見つけます。行数の推定値を実際の行数と照らし合わせ、バッファ動作がどこで発生しているか確認します。
  3. ルートノードの共有アクセス数と、繰り返し測定した実行時間を比較します。ノード名が変わっただけでは、有用な改善とは言えません。

これはフェーズごとに行うウォームキャッシュでの比較です。行の挿入、正しさを確認するクエリ、ウォームアップ、インデックス作成は、いずれもキャッシュ状態に影響します。両フェーズでMacの負荷とデータベース設定を同じに保ちます。異常に遅い実行も見えるよう、7回分の個別観測値を保持します。中央値だけではばらつきが隠れる場合があります。

さらに確かめるには、実験全体を繰り返し、別のカテゴリや上限件数も試します。より大きな上限件数を試す場合は、別のワークロードとして示してください。元の比較で分かるのは、観測されたローカル環境下で、このインデックスが50件の記事を要求する場合に役立つかどうかです。

作業量の減少とバッファの温まりをどう見分けますか？

以下の数値は説明用の入力であり、実測結果ではありません。読み取り、ヒット、時間を一緒に見る理由を示しています。

説明用の実行 実行時間 共有ヒット 共有読み取り ヒット＋読み取り
A、最初の観測 12 ms 900 100 1,000
A、繰り返し観測 10 ms 1,000 0 1,000
B、候補プラン 3 ms 80 0 80

Aの2回の観測を比べると、読み取りはなくなっていますが、共有アクセスの合計は1,000のままです。これはキャッシュ状態の変化と整合しますが、共有バッファへのアクセス量の減少を示すものではありません。Bは実行時間の短縮とアクセス数の減少を伴います。同等の結果が得られる状態でこの傾向が繰り返されるなら、このタスクにおいてBの共有バッファ作業量が少ないという限定的な結論を裏付けます。

実測結果が異なる場合は、次の判断表を使います。

観測された傾向 解釈と次の対応
時間が短く、共有アクセスも少ない テストしたワークロードで改善した証拠です。再現性を確認するため繰り返します
時間が短く、読み取りが少ないが、アクセス合計はほぼ同じ キャッシュ状態が変化を説明する可能性があります。ウォームアップ後の繰り返しを比較します
アクセスは少ないが、時間は同程度か長い 選択する前に、ソート、式、計時のばらつきを調べます
読み取りはゼロだが、ヒットは多い キャッシュ済みの実行でもバッファアクセスは発生します。動作の多いノードを調べます
一時読み取りまたは一時書き込みが発生する 設定を変更する前に、それを発生させた作業を調べます

キャッシュヒット率だけで判断しないでください。説明例では、ウォームアップ後のAもBも共有バッファのヒット率は100%ですが、アクセス数には大きな差があります。時間とあわせて、生の数値と結果の件数も記録してください。

これらの測定値はアプリケーションの速度とどう関係しますか？

サーバー側のExecution Timeには計画時間が含まれません。また、通常のEXPLAIN ANALYZEでは結果の送信も、デフォルトでは出力変換も計測されません。計測処理自体にもオーバーヘッドがあります。そのため、このスクリプトが比較するのは、記事一覧を実際に表示するまでの時間全体ではなく、計測処理を加えたデータベース実行です。PostgreSQL: Using EXPLAIN

PostgreSQL 17または18で出力変換を個別に調べるには、同じEXPLAINオプションにSERIALIZE TEXTを追加します。PostgreSQL 16のドキュメントにはこのオプションがありません。シリアライズをしてもクエリ結果の行は送信されないため、ネットワーク転送時間は測定できません。比較する実行ではシリアライズ設定を同じにします。PostgreSQL 17: EXPLAIN、PostgreSQL 16: EXPLAIN

アプリケーションで測定するには、接続を確立した状態で、通常の記事SELECTのドライバー呼び出しをawaitし、その完了までの時間を計ります。これを別個のクライアント経過時間として記録します。アプリケーションで実際に使うクエリ設定を維持してください。node-postgresのクエリにnameを加えると、接続ごとのプリペアドステートメント動作が有効になり、比較条件が変わります。node-postgres: Queries

同等の結果、繰り返し測定した時間、バッファ数から、提供したい記事リクエストに対して一貫した傾向が読み取れる場合に、候補を選びます。結論を特定の条件に結び付け、再現できるよう、完全なプラン、個々の実行結果、サーバーバージョン、クエリパラメーターを保管してください。

出典