そのクエリ、100倍速くなりますよ — PostgreSQL 1000万行テーブルをチューニングした全記録

「SQLが遅い」という報告は、サービスが成長する過程で必ず直面する課題だ。特にテーブルの行数が数百万行を超えたあたりから、今まで問題なかったクエリが突然遅くなることがある。この記事では、1000万行のordersテーブルを題材に、クエリ実行時間を25秒から0.25秒に短縮した実践的なチューニング手法を解説する。

問題のクエリ: 25秒かかるレポート生成

以下のクエリは、特定期間の注文データを集計してレポートを生成するものだ。テーブル行数が1000万行を超えたあたりから実行時間が25秒以上かかるようになった。

SELECT
  DATE_TRUNC('day', created_at) AS order_date,
  status,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
  AND site_id = 42
  AND status IN ('completed', 'shipped')
GROUP BY DATE_TRUNC('day', created_at), status
ORDER BY order_date DESC;

ステップ1: EXPLAIN ANALYZEで現状を把握する

チューニングの第一歩は、PostgreSQLのクエリプランナーが何をしているかを理解することだ。EXPLAIN ANALYZEを使って実行計画を取得する。

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  DATE_TRUNC('day', created_at) AS order_date,
  status,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'
  AND site_id = 42
  AND status IN ('completed', 'shipped')
GROUP BY DATE_TRUNC('day', created_at), status
ORDER BY order_date DESC;

結果は以下の通りだった。

Sort  (cost=890234.56..890234.58 rows=8 width=48) (actual time=25134.567..25134.572 rows=62 loops=1)
  Sort Key: (date_trunc('day', created_at)) DESC
  Sort Method: quicksort  Memory: 32kB
  ->  HashAggregate  (cost=890234.10..890234.40 rows=8 width=48) (actual time=25134.501..25134.530 rows=62 loops=1)
        Group Key: date_trunc('day', created_at), status
        ->  Seq Scan on orders  (cost=0.00..876543.00 rows=182345 width=16) (actual time=0.045..24890.123 rows=156789 loops=1)
              Filter: ((created_at >= '2026-01-01' AND created_at <= '2026-01-31') AND (site_id = 42) AND (status = ANY ('{completed,shipped}')))
              Rows Removed by Filter: 9843211
              Buffers: shared hit=12345 read=654321
Planning Time: 0.234 ms
Execution Time: 25134.678 ms

問題点は明白だ。Seq Scan（全テーブルスキャン）が実行されている。1000万行すべてを読み込み、そのうち984万行をフィルタで除外している。これは極めて非効率だ。

ステップ2: 適切なインデックスを設計する

このクエリのWHERE句を分析すると、3つの条件がある。site_id、created_at、statusだ。これらを組み合わせた複合インデックスが有効だ。

インデックスの列順序は重要

複合インデックスの列順序は、等値条件の列を先に、範囲条件の列を後にするのが原則だ。

-- site_id(等値) → status(等値/IN) → created_at(範囲) の順
CREATE INDEX CONCURRENTLY idx_orders_site_status_created
ON orders (site_id, status, created_at);

CONCURRENTLY オプションを使うことで、インデックス作成中もテーブルへの書き込みがブロックされない。本番環境では必須のオプションだ。ただし、作成時間は通常の2〜3倍かかる。

カバリングインデックスでIndex Only Scanを狙う

さらに最適化するため、SELECT句で使われる列もインデックスに含めるカバリングインデックスを作成する。

-- INCLUDE句でtotal_amountをインデックスに含める
CREATE INDEX CONCURRENTLY idx_orders_covering
ON orders (site_id, status, created_at)
INCLUDE (total_amount);

これにより、PostgreSQLはテーブル本体にアクセスせずにインデックスだけでクエリを完了できるIndex Only Scanが可能になる。

ステップ3: インデックス作成後の確認

インデックスを作成した後、再びEXPLAIN ANALYZEを実行する。

Sort  (cost=1234.56..1234.58 rows=8 width=48) (actual time=245.567..245.572 rows=62 loops=1)
  Sort Key: (date_trunc('day', created_at)) DESC
  Sort Method: quicksort  Memory: 32kB
  ->  HashAggregate  (cost=1234.10..1234.40 rows=8 width=48) (actual time=245.501..245.530 rows=62 loops=1)
        Group Key: date_trunc('day', created_at), status
        ->  Index Only Scan using idx_orders_covering on orders  (cost=0.56..1100.00 rows=182345 width=16) (actual time=0.034..198.123 rows=156789 loops=1)
              Index Cond: ((site_id = 42) AND (status = ANY ('{completed,shipped}')) AND (created_at >= '2026-01-01') AND (created_at <= '2026-01-31'))
              Heap Fetches: 0
              Buffers: shared hit=4321
Planning Time: 0.345 ms
Execution Time: 245.678 ms

Seq Scanがindex Only Scanに変わり、実行時間が25秒から0.25秒に短縮された。約100倍の高速化だ。Heap Fetches: 0はテーブル本体へのアクセスが発生していないことを意味し、カバリングインデックスが正しく機能していることがわかる。

ステップ4: パーティショニングの検討

行数がさらに増えて1億行を超えるような場合は、テーブルパーティショニングを検討する。created_atでの範囲パーティショニングが有効だ。

-- パーティションテーブルの作成
CREATE TABLE orders_partitioned (
  id BIGSERIAL,
  site_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  -- 他のカラム...
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 月次パーティションの作成
CREATE TABLE orders_2026_01 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders_2026_02 PARTITION OF orders_partitioned
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- 以降の月も同様に作成

パーティショニングにより、特定期間のクエリはその期間に対応するパーティションのみスキャンされる。パーティションプルーニングと呼ばれるこの最適化は、大量データの集計クエリに絶大な効果を発揮する。

ステップ5: postgresql.confのチューニング

インデックス設計と並んで重要なのが、PostgreSQLサーバー自体のパラメータチューニングだ。

# メモリ関連
shared_buffers = '4GB'           # RAMの25%が目安
effective_cache_size = '12GB'    # RAMの75%が目安
work_mem = '256MB'               # ソートやハッシュ操作に使用
maintenance_work_mem = '1GB'     # VACUUM, CREATE INDEX用

# プランナー関連
random_page_cost = 1.1           # SSD使用時は1.0〜1.1に下げる
effective_io_concurrency = 200   # SSD使用時は200に

# WAL関連
wal_buffers = '64MB'
checkpoint_completion_target = 0.9
max_wal_size = '4GB'

# 並列クエリ
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost = 0.01
parallel_setup_cost = 100

work_memの設定に注意

work_memはセッション単位で使用されるメモリだ。値を大きくすればソートやハッシュジョインが高速になるが、同時接続数 × work_memの合計がサーバーのRAMを超えないように注意する必要がある。

ステップ6: 統計情報の更新

PostgreSQLのクエリプランナーは、テーブルの統計情報を基に実行計画を決定する。統計情報が古いと、最適でない実行計画が選択される可能性がある。

-- 統計情報を手動で更新
ANALYZE orders;

-- 特定カラムの統計精度を上げる
ALTER TABLE orders ALTER COLUMN site_id SET STATISTICS 1000;
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 1000;
ANALYZE orders;

default_statistics_targetのデフォルト値は100だが、データの分布が偏っているカラムでは1000に上げることで、プランナーがより正確な見積もりを行える。

ステップ7: pg_stat_statementsで遅いクエリを特定する

チューニングすべきクエリを特定するには、pg_stat_statementsエクステンションが不可欠だ。

-- エクステンションの有効化
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 総実行時間が長いクエリTOP10
SELECT
  query,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_time_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

このクエリにより、システム全体で最も時間を消費しているクエリを特定できる。呼び出し回数が多いクエリは、1回あたりの実行時間が短くても総実行時間では上位に来ることがある。