記事本文を選択すると、インデックスオンリースキャンが使えなくなることがあります。PostgreSQLはインデックスに含まれていない列を取得する必要があるためです。このガイドでは、Macでこの変化を診断し、Node.jsでその影響を測定する方法を説明します。インデックスオンリースキャンの要件。

選択する列を変えるとプランが変わるのはなぜですか？

id、category、bodyを含む記事テーブルと、(category, id)のB-treeインデックスを考えてみましょう。カテゴリで絞り込んでIDを選択するクエリは、フィルター値と結果の両方をそのインデックスから取得できます。bodyを追加すると、そのインデックスだけではインデックスオンリースキャンに不十分になります。PostgreSQLのインデックスは、ヒープと呼ばれるテーブルの主ストレージとは別に保存されます。通常のインデックススキャンでは、テーブルデータも取得します。インデックスオンリースキャンとカバリングインデックス。

プランナーは、必要なテーブル行を取得する方法を比較するようになります。Index Scanを維持する場合もあれば、Bitmap Index ScanとBitmap Heap Scanを組み合わせたり、Seq Scanを選んだりする場合もあります。ビットマップスキャンは、物理的な位置ごとにヒープへのアクセスをまとめます。離れた場所にある多数の行を取得するときは、個別に取得するよりテーブル全体をスキャンするほうが安く済むことがあります。最上位のスキャンノードにヒープと表示されていても、ビットマッププランはインデックスを使用しています。EXPLAINの使用。

また、元のプランも注意深く確認してください。Index Only Scanであっても、Heap Fetchesがゼロ以外になることがあります。可視性を確認するために、PostgreSQLがテーブル行を参照する必要が生じる場合があるためです。こうした参照を避けるには、必要な列がインデックスに含まれていることに加えて、テーブルの可視性マップも関係します。インデックスオンリースキャンの可視性チェック。

Macで比較を再現する

ローカルで稼働中のPostgreSQLデータベースと、インストール済みのサポート対象Node.jsリリースおよびnpmを使用します。基本例ではPostgreSQL 14～18で利用できる機能を使います。参照した現行のPostgreSQLマニュアルにはバージョン18と記載されています。以下の追加のシリアライズ診断には、PostgreSQL 17以降が必要です。node-postgresのドキュメントには、Node.jsとの互換性とインストールコマンドが記載されています。PostgreSQLのドキュメント、node-postgresのインストールと互換性。

Terminalを開き、新しいフォルダーで作業します。以下のコマンドでプロジェクトを作成し、データベースドライバーをインストールします。

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

このプログラムは、PGHOST、PGPORT、PGDATABASE、PGUSERを通じて、既存のローカル接続設定を使用します。すでに接続できるデータベース向けに設定してください。明示的な接続オプションを指定せずにClientを作成すると、node-postgresはこれらの環境変数を読み込みます。環境変数を使った接続。

次の完全なプログラムをarticle-plan.cjsとして保存します。合成データの行を一時テーブルに作成し、インデックスを構築して、両方の実行プランを出力したあと、通常のクエリを両方計測します。どちらの比較も、同じ接続とカテゴリ値を使います。クエリ設定では、文書化されているnode-postgresのAPIに従い、SQLをtextで、パラメーターをvaluesで指定します。クエリ設定。

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  const client = new Client();
  try {
    await client.connect();
    const version = await client.query('SHOW server_version');
    console.log(version.rows[0]);

    await client.query(`CREATE TEMP TABLE article_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

同じフォルダーから実行します。

node article-plan.cjs

セットアップではVACUUM (ANALYZE)を単独のコマンドとして送信します。このコマンドはVACUUMのあとに統計情報の収集を行い、トランザクションブロック内では実行できません。例にトランザクションラッパーを追加する場合も、このコマンドはその外で実行してください。VACUUM。

JavaScriptの構文チェックは通過し、別途行った決定的な計算で、フィクスチャの行数と本文のバイト数の合計を確認しました。調査環境ではPostgreSQLを利用できなかったため、データベースクエリは確認しましたが、ここでは実行していません。特定のプランや実行時間を、実測結果として提示しているわけではありません。

プランを役立つ順序で読む

まずスキャン名を確認し、次に推定行数と実際の行数を比較します。そのあと、テーブルアクセスとバッファーの動きを調べます。この順序で見ると、利用可能なアクセス方法の変化と、不正確な推定や結果取得にかかるコストとを区別できます。VERBOSEでは、各ノードの出力列も確認できます。EXPLAINの使用、EXPLAINのオプション。

観察内容 解釈 次に確認すること
IDの取得にはIndex Only Scan、本文の取得にはIndex Scanが使われる 本文クエリにはテーブルデータが必要 バッファーと通常クエリの実行時間を比較する
本文の取得にBitmap Heap Scanが使われる 子ノードでインデックスの使用状況を確認する ヒープブロック数と返された行数を比較する
本文の取得にSeq Scanが使われる プランナーはテーブルスキャンを優先している 推定行数と一致した実際の行数を比較する
IDの取得でHeap Fetchesがかなり多い インデックスオンリー実行でもヒープを参照している 最近のテーブル変更が影響しているか確認する
推定行数と実際の行数が大きく異なる 統計情報がデータの状態を十分に表していない可能性がある 統計情報を収集して、もう一度比較する

ANALYZEは、プランナーがプランを選ぶ際に使用する統計情報を収集します。合成データのカテゴリ分布を変更した場合は、次の比較を解釈する前に統計情報を再収集してください。サンプリングのため、推定値は実行ごとに多少変動することがあります。ANALYZE。

widthは、実際に転送されたバイト数の測定値ではなく、行あたりの出力バイト数の推定値として扱ってください。同様に、プランナーのコストはミリ秒ではなく相対単位です。バッファーヒットは、PostgreSQLのキャッシュ内でブロックが見つかったことを示します。作業が発生しなかったという意味ではありません。プランの推定値、バッファーの報告。

このフィクスチャでは、どちらのクエリも5,000行を返すはずです。合成した本文はそれぞれ1,160 ASCIIバイトなので、本文クエリが返す本文データは5,800,000バイト、約5.53 MiBです。IDとプロトコルのオーバーヘッドは含みません。これはサンプル入力に基づく計算であり、データベースの性能測定ではありません。プログラムのbodyBytesフィールドを使えば、この違いを確認できます。

通常のクエリはプラン測定のあとに実行されるため、その経過時間には、キャッシュが温まった順序での実行が反映されます。わずかな時間差から結論を出す前に、比較を繰り返し、実行順序を入れ替えてください。単一の実行は診断には役立ちますが、信頼できる性能比を示すものではありません。

本文の取得に、EXPLAINが示す以上の時間がかかるのはなぜですか？

大きなテキスト値は、圧縮されたり、PostgreSQLのTOAST機構によってメインテーブルの外に保存されたりすることがあります。そのため、行のメインエントリーを取得する処理と、完全な本文をクライアント向けに生成する処理では、作業量が異なる場合があります。上記の短くて繰り返しの多いフィクスチャは、列のカバレッジを示すためのもので、あらゆる本文の保存パターンを表すものではありません。TOASTストレージ。

デフォルトでは、EXPLAIN ANALYZEはクエリ結果を破棄し、配信用の変換を行いません。PostgreSQL 17以降では、SERIALIZE TEXTを追加すると、必要に応じた行外保存値の取得を含め、出力変換を調べられます。このオプションはPostgreSQL 17で導入されました。EXPLAINのシリアライズ動作、PostgreSQL 17のリリースノート。

試すには、プログラム内の既存のEXPLAINテンプレート行をこの断片に置き換え、プログラム全体を再実行します。

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

PostgreSQL 14～16では元の行をそのまま使い、通常クエリのタイマーで結果の受信を測定してください。SERIALIZEは引き続き結果データをクライアントに送信しないため、転送やJavaScriptでの処理は測定できません。EXPLAINの限界。

node-postgresは通常、結果の行をJavaScriptオブジェクトに格納します。そのrowMode: 'array'オプションは結果の表現を変えますが、SQLの結果から本文の値を除外するものではありません。そのため、このクエリにbodyを追加したことで失われた列のカバレッジを取り戻すことはできません。行モード。

記事のタスクに合った変更を選ぶ

デスクトップ上のタスクでIDの一覧だけが必要なら、選択する列を絞ったままにします。記事の全文が必要なら、本文クエリの実際の経過時間と結果サイズを評価してください。診断の目的は、求められた結果を維持しながら、不要な作業を特定することです。

カテゴリ内の先頭50件だけを表示する画面では、要求する結果を制限する方法が有効な選択肢になります。プログラムのqueries配列で、本文クエリの文字列を次のSQLに置き換えます。

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

これにより、条件に一致するすべての記事を取得するタスクから、順序付きの1ページを取得するタスクに意図的に変更します。カテゴリが固定されていれば、既存の(category, id)インデックスはID順に取得する候補経路になります。適合するB-treeインデックスがあれば、LIMITを使うORDER BYを効率化できます。必要な行数に達した時点で実行を止められるためです。選択されたプランは、引き続き確認が必要です。インデックスとORDER BY。

INCLUDE (body)を自動的に解決する方法だと考えないでください。インクルード列はテーブルデータを重複して保持してインデックスを大きくし、インデックスタプルがサイズ上限を超えると挿入が失敗することがあります。小さなペイロード列は、測定を伴う実験の候補として有効な場合があります。INCLUDEはPostgreSQL 11で導入され、それ以前のバージョンでは利用できません。カバリングインデックスの注意点、PostgreSQL 11のリリースノート。

調査の最後に、次の3点を具体的に確認します。本文を選択したことでインデックスオンリーの対象から外れたか、新しいスキャンが許容できる性能か、そして完全な結果がNode.jsに届くまでにどれだけ時間がかかるかです。これらの結果をもとに、プランを維持するか、取得する記事を減らすか、実際のクエリ用に別のインデックスを試すかを判断できます。

参考資料