myougaTheAxo

Posted on Mar 11 • Originally published at zenn.dev

Claude CodeでPostgreSQLインデックス設計を最適化する：複合・部分・EXPLAIN ANALYZE

#claudecode #postgres #typescript #performance

Claude Codeを使ってPostgreSQLのインデックス設計を体系的に最適化する方法を紹介します。クエリ先行設計からEXPLAIN ANALYZEの自動化まで、実務で使えるパターンをまとめました。

CLAUDE.mdルール：インデックス設計の原則

プロジェクトのCLAUDE.mdに以下のルールを定義しておくと、Claude Codeが一貫した設計を提案してくれます。

## PostgreSQL インデックス設計ルール

1. クエリ先行設計: インデックスはクエリのWHERE/ORDER BY/JOINを先に分析してから設計する
2. カーディナリティ順: 複合インデックスは 等値(=) → 範囲(<,>,BETWEEN) → ソート(ORDER BY) の順で列を並べる
3. 部分インデックス: WHERE条件が固定されるクエリには部分インデックスを検討する
4. 未使用インデックス監視: pg_stat_user_indexesで定期的に使用状況を確認する

このルールがあるだけで、Claude Codeのインデックス提案の質が大きく変わります。

4つのSQLパターン

1. 複合インデックス（Composite Index）

カーディナリティ順を守った複合インデックスの例です。

-- クエリ先行設計の例
-- 対象クエリ:
-- SELECT * FROM orders
-- WHERE user_id = $1
--   AND order_date BETWEEN $2 AND $3
-- ORDER BY order_date DESC;

-- 正しい列順: 等値(user_id) → 範囲(order_date)
CREATE INDEX idx_orders_user_date
  ON orders (user_id, order_date DESC);

-- 誤った列順（範囲が先だとuser_idのインデックスが効かない）
-- CREATE INDEX idx_orders_date_user ON orders (order_date, user_id); -- NG

2. 部分インデックス（Partial Index）

WHERE条件が固定されているクエリには部分インデックスが効果的です。

-- 対象クエリ: アクティブなユーザーのみを頻繁に検索する
-- SELECT * FROM users WHERE status = 'active' AND email = $1;

-- 部分インデックス: activeレコードのみをインデックス化
CREATE INDEX idx_users_active_email
  ON users (email)
  WHERE status = 'active';

-- 全レコードのインデックスと比較:
-- 全ユーザー100万件のうちアクティブが10万件なら、インデックスサイズが1/10になる
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexname IN ('idx_users_active_email');

3. 関数インデックス（Functional Index）

大文字小文字を区別しない検索には関数インデックスを使います。

-- 大文字小文字を区別しないメール検索
-- SELECT * FROM users WHERE lower(email) = lower($1);

-- lower()を使った関数インデックス
CREATE INDEX idx_users_email_lower
  ON users (lower(email));

-- JSONフィールドへのインデックス
CREATE INDEX idx_orders_metadata_type
  ON orders ((metadata->>'order_type'));

-- 複合関数インデックス
CREATE INDEX idx_products_search
  ON products (lower(name), category_id)
  WHERE deleted_at IS NULL;

4. カバリングインデックス（Covering Index with INCLUDE）

INCLUDEを使うとIndex Only Scanが可能になり、テーブルアクセスをゼロにできます。

-- 対象クエリ:
-- SELECT user_id, total_amount, status
-- FROM orders
-- WHERE user_id = $1 AND created_at > $2;

-- INCLUDEで追加列をインデックスに含める
-- total_amountとstatusはWHEREに使わないが、SELECT対象なのでINCLUDEに入れる
CREATE INDEX idx_orders_covering
  ON orders (user_id, created_at)
  INCLUDE (total_amount, status);

-- EXPLAIN ANALYZEでIndex Only Scanを確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, total_amount, status
FROM orders
WHERE user_id = 123 AND created_at > '2026-01-01';
-- "Index Only Scan using idx_orders_covering" が出ればOK

TypeScript: QueryAnalyzerクラス

EXPLAIN ANALYZEを自動化するTypeScriptクラスです。Claude Codeにこのクラスを使わせることで、クエリ分析を標準化できます。

import { Pool, QueryResult } from 'pg';

interface ExplainNode {
  'Node Type': string;
  'Startup Cost': number;
  'Total Cost': number;
  'Plan Rows': number;
  'Actual Rows'?: number;
  'Actual Total Time'?: number;
  'Shared Hit Blocks'?: number;
  'Shared Read Blocks'?: number;
  Plans?: ExplainNode[];
  [key: string]: unknown;
}

interface ExplainResult {
  Plan: ExplainNode;
  'Planning Time': number;
  'Execution Time': number;
}

interface QueryAnalysis {
  query: string;
  params: unknown[];
  executionTime: number;
  planningTime: number;
  totalCost: number;
  indexesUsed: string[];
  seqScans: string[];
  bufferHitRatio: number;
  recommendations: string[];
}

export class QueryAnalyzer {
  constructor(private pool: Pool) {}

  async analyze(query: string, params: unknown[] = []): Promise<QueryAnalysis> {
    const explainQuery = `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${query}`;
    const result: QueryResult = await this.pool.query(explainQuery, params);
    const explainData: ExplainResult = result.rows[0]['QUERY PLAN'][0];

    const plan = explainData.Plan;
    const indexesUsed: string[] = [];
    const seqScans: string[] = [];

    this.traversePlan(plan, indexesUsed, seqScans);

    const sharedHit = plan['Shared Hit Blocks'] ?? 0;
    const sharedRead = plan['Shared Read Blocks'] ?? 0;
    const totalBlocks = sharedHit + sharedRead;
    const bufferHitRatio = totalBlocks > 0 ? sharedHit / totalBlocks : 1;

    const recommendations = this.generateRecommendations(
      seqScans,
      bufferHitRatio,
      explainData['Execution Time']
    );

    return {
      query,
      params,
      executionTime: explainData['Execution Time'],
      planningTime: explainData['Planning Time'],
      totalCost: plan['Total Cost'],
      indexesUsed,
      seqScans,
      bufferHitRatio,
      recommendations,
    };
  }

  private traversePlan(
    node: ExplainNode,
    indexesUsed: string[],
    seqScans: string[]
  ): void {
    const nodeType = node['Node Type'];

    if (nodeType.includes('Index')) {
      const indexName = node['Index Name'] as string | undefined;
      if (indexName) indexesUsed.push(indexName);
    }

    if (nodeType === 'Seq Scan') {
      const relationName = node['Relation Name'] as string | undefined;
      if (relationName) seqScans.push(relationName);
    }

    if (node.Plans) {
      for (const childPlan of node.Plans) {
        this.traversePlan(childPlan, indexesUsed, seqScans);
      }
    }
  }

  private generateRecommendations(
    seqScans: string[],
    bufferHitRatio: number,
    executionTime: number
  ): string[] {
    const recommendations: string[] = [];

    if (seqScans.length > 0) {
      recommendations.push(
        `Sequential scan detected on: ${seqScans.join(', ')}. Consider adding indexes.`
      );
    }

    if (bufferHitRatio < 0.95) {
      recommendations.push(
        `Low buffer hit ratio (${(bufferHitRatio * 100).toFixed(1)}%). Consider increasing shared_buffers.`
      );
    }

    if (executionTime > 100) {
      recommendations.push(
        `High execution time (${executionTime.toFixed(2)}ms). Review query and index design.`
      );
    }

    return recommendations;
  }

  async assertQueryCost(
    query: string,
    params: unknown[],
    maxCost: number
  ): Promise<void> {
    const analysis = await this.analyze(query, params);

    if (analysis.totalCost > maxCost) {
      throw new Error(
        `Query cost ${analysis.totalCost} exceeds threshold ${maxCost}.\n` +
        `Seq scans: ${analysis.seqScans.join(', ')}\n` +
        `Recommendations: ${analysis.recommendations.join('; ')}`
      );
    }

    if (analysis.seqScans.length > 0) {
      throw new Error(
        `Sequential scan detected on: ${analysis.seqScans.join(', ')}`
      );
    }
  }
}

未使用インデックスの検出

pg_stat_user_indexesを使って、使われていないインデックスを定期的に検出します。

-- 未使用インデックスの検出クエリ
-- スキャン回数10回未満かつサイズ1MB超のインデックスを抽出
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan AS scan_count,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  pg_relation_size(indexrelid) AS index_size_bytes
FROM pg_stat_user_indexes
WHERE
  idx_scan < 10
  AND pg_relation_size(indexrelid) > 1024 * 1024
  AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重複インデックスの検出
SELECT
  indrelid::regclass AS table_name,
  array_agg(indexrelid::regclass) AS duplicate_indexes,
  array_agg(indkey) AS index_columns
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

TypeScriptでの定期実行例：

async function detectUnusedIndexes(pool: Pool): Promise<void> {
  const result = await pool.query(`
    SELECT
      schemaname,
      tablename,
      indexname,
      idx_scan AS scan_count,
      pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
    FROM pg_stat_user_indexes
    WHERE
      idx_scan < 10
      AND pg_relation_size(indexrelid) > 1024 * 1024
      AND indexname NOT LIKE '%_pkey'
    ORDER BY pg_relation_size(indexrelid) DESC
  `);

  if (result.rows.length > 0) {
    console.warn('Potentially unused indexes detected:');
    for (const row of result.rows) {
      console.warn(
        `  ${row.schemaname}.${row.tablename}.${row.indexname}: ` +
        `${row.scan_count} scans, ${row.index_size}`
      );
    }
  }
}

CI EXPLAIN チェックの組み込み

GitHub ActionsでEXPLAINコストチェックをCIに組み込む例です。

// tests/query-performance.test.ts
import { Pool } from 'pg';
import { QueryAnalyzer } from '../src/query-analyzer';

describe('Query Performance Checks', () => {
  let pool: Pool;
  let analyzer: QueryAnalyzer;

  beforeAll(async () => {
    pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
    analyzer = new QueryAnalyzer(pool);
  });

  afterAll(async () => {
    await pool.end();
  });

  it('orders by user_id query should use index', async () => {
    await analyzer.assertQueryCost(
      'SELECT * FROM orders WHERE user_id = $1 AND order_date > $2',
      [1, '2026-01-01'],
      1000
    );
  });

  it('active users email lookup should use partial index', async () => {
    const analysis = await analyzer.analyze(
      "SELECT * FROM users WHERE status = 'active' AND lower(email) = $1",
      ['test@example.com']
    );

    expect(analysis.seqScans).toHaveLength(0);
    expect(analysis.executionTime).toBeLessThan(50);
  });
});

まとめ

Claude Codeを使ったPostgreSQLインデックス最適化のポイントは4つです。

クエリ先行設計: インデックスはクエリのWHERE/ORDER BY/JOINを分析してから設計する。CLAUDE.mdにルールを書いておくとClaude Codeが一貫した提案をしてくれる
INCLUDEでIndex Only Scan: 頻繁に取得するカラムをINCLUDEに追加するだけで、テーブルへのアクセスが完全になくなりパフォーマンスが劇的に改善する
未使用インデックス検出: pg_stat_user_indexesで定期監視し、不要なインデックスを削除する。インデックスはINSERT/UPDATE時のオーバーヘッドになるため、使われていないものは削除が正解
CI EXPLAIN チェック: QueryAnalyzerをテストに組み込み、コスト閾値超過やSeq Scanを自動検出する。インデックスの劣化をCIで早期発見できる

Claude Codeのコードレビュー機能を最大化するプロンプトパックを販売中です。

Code Review Pack ¥980 — SQLクエリ最適化、パフォーマンス分析、セキュリティレビューのプロンプト集
prompt-works.jp で検索：「Code Review Pack」

DEV Community