PostgreSQLクエリ最適化とパフォーマンスチューニング実践ガイド：EXPLAINからパーティショニングまで

-- 基本的なEXPLAIN ANALYZEの使用
EXPLAIN ANALYZE
SELECT u.id, u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.total_amount DESC
LIMIT 100;

Limit  (cost=15234.56..15234.81 rows=100 width=52) (actual time=45.123..45.156 rows=100 loops=1)
  ->  Sort  (cost=15234.56..15347.89 rows=45332 width=52) (actual time=45.121..45.142 rows=100 loops=1)
        Sort Key: o.total_amount DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Hash Join  (cost=3456.78..13890.12 rows=45332 width=52) (actual time=12.345..38.901 rows=45332 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..9876.54 rows=45332 width=20) (actual time=0.015..18.234 rows=45332 loops=1)
                    Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
                    Rows Removed by Filter: 154668
              ->  Hash  (cost=2345.00..2345.00 rows=100000 width=36) (actual time=12.123..12.123 rows=100000 loops=1)
                    Buckets: 131072  Batches: 1  Memory Usage: 6234kB
                    ->  Seq Scan on users u  (cost=0.00..2345.00 rows=100000 width=36) (actual time=0.008..5.678 rows=100000 loops=1)
Planning Time: 0.456 ms
Execution Time: 45.234 ms

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders
WHERE user_id = 12345
  AND created_at BETWEEN '2026-01-01' AND '2026-03-14';

Index Scan using idx_orders_user_created on orders  (cost=0.43..8.45 rows=1 width=64) (actual time=0.023..0.025 rows=3 loops=1)
  Index Cond: ((user_id = 12345) AND (created_at >= '2026-01-01') AND (created_at <= '2026-03-14'))
  Buffers: shared hit=4
Planning Time: 0.123 ms
Execution Time: 0.045 ms

-- 単一カラムB-treeインデックス
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 複合インデックス：先行カラムの順序が重要
-- WHERE user_id = ? AND created_at >= ? 形式のクエリに最適
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- カバリングインデックス：Index-Only Scanを誘導
CREATE INDEX idx_orders_covering ON orders (user_id, created_at)
  INCLUDE (status, total_amount);

-- 部分インデックス：特定条件の行のみインデックスしてサイズを削減
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
  WHERE status = 'active';

-- JSONBフィールドに対するGINインデックス
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- jsonb_path_ops：特定の演算子でより効率的、インデックスサイズが小さい
CREATE INDEX idx_products_metadata_path ON products USING GIN (metadata jsonb_path_ops);

-- 全文検索用GINインデックス
CREATE INDEX idx_articles_search ON articles USING GIN (
  to_tsvector('korean', title || ' ' || content)
);

-- 配列カラムに対するGIN
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- PostGIS空間インデックス
CREATE INDEX idx_stores_location ON stores USING GiST (location);

-- 範囲型インデックス
CREATE INDEX idx_reservations_period ON reservations USING GiST (
  tstzrange(start_time, end_time)
);

-- 範囲の重なり検索
SELECT * FROM reservations
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-14 09:00', '2026-03-14 18:00');

-- 時系列ログテーブルにBRINインデックス
CREATE INDEX idx_logs_created_brin ON access_logs USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- BRINが効果的か確認：相関係数が1に近いほど良い
SELECT correlation
FROM pg_stats
WHERE tablename = 'access_logs' AND attname = 'created_at';

-- Bad：アプリケーションからN+1パターンで実行されるクエリ
-- 1) SELECT * FROM users WHERE department = 'engineering';  -- 50名返却
-- 2) SELECT * FROM orders WHERE user_id = 1;  -- 50回繰り返し実行
-- 3) SELECT * FROM orders WHERE user_id = 2;
-- ...

-- Good：JOINで一度に取得
SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.department = 'engineering';

-- Good：LATERAL JOINでユーザーごとに直近3件の注文のみ取得
SELECT u.id, u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
  SELECT o.id, o.total_amount, o.created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent_orders
WHERE u.department = 'engineering';

-- Bad：相関サブクエリ - 外部クエリの各行ごとにサブクエリが実行される可能性
SELECT u.id, u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE u.status = 'active';

-- Good：GROUP BYとJOINの活用
SELECT u.id, u.name, COALESCE(oc.cnt, 0) AS order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY user_id
) oc ON u.id = oc.user_id
WHERE u.status = 'active';

-- 大容量データではEXISTSが有利な場合が多い
-- EXISTSは最初のマッチを見つけたら即座に中断する
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.created_at >= '2026-01-01'
);

-- INはサブクエリの結果が小さい場合に有利
SELECT u.id, u.name
FROM users u
WHERE u.department_id IN (
  SELECT id FROM departments WHERE region = 'APAC'
);

-- Bad：OFFSETが大きくなるほど遅くなる
SELECT * FROM orders
ORDER BY id
OFFSET 1000000 LIMIT 10;

-- Good：キーセットページネーション - 常に一定のパフォーマンス
SELECT * FROM orders
WHERE id > 1000000  -- 前のページの最後のid
ORDER BY id
LIMIT 10;

-- 複合ソート基準でのキーセットページネーション
SELECT * FROM orders
WHERE (created_at, id) > ('2026-03-13 15:30:00', 98765)
ORDER BY created_at, id
LIMIT 10;

# postgresql.conf - Autovacuumチューニング推奨設定

# Autovacuumを有効化（絶対にオフにしない）
autovacuum = on

# 同時実行可能なautovacuum worker数
# CPUコア数とテーブル数を考慮して設定
autovacuum_max_workers = 5

# VACUUMトリガー閾値：dead tupleがテーブルの10% + 50件超過時に実行
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1

# 大容量テーブルではscale_factorを下げる必要がある
# 1億件テーブルで0.2（デフォルト）だと2千万件が溜まるまでVACUUMが実行されない

# ANALYZEトリガー：変更がテーブルの5% + 50件超過時に統計更新
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

# コストベースの遅延：高いほどVACUUMは速いがI/O負荷が増加
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000

# XID Wraparound防止のための設定
autovacuum_freeze_max_age = 300000000

-- 大容量注文テーブル：より頻繁にVACUUMを実行
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,      -- 1%変更でもVACUUM
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 0             -- 最大速度でVACUUM
);

-- VACUUMステータスのモニタリング
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_ratio_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 月別Rangeパーティショニングテーブル作成
CREATE TABLE events (
  id          BIGSERIAL,
  event_type  VARCHAR(50) NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 月別パーティション作成
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 各パーティションにインデックス作成（親テーブルに作成すれば自動継承）
CREATE INDEX idx_events_type_created ON events (event_type, created_at);

-- パーティションプルーニングの確認：2026年3月のデータのみ参照
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
  AND event_type = 'purchase';

-- pg_partmanインストール後の自動管理設定
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => '1 month',
  p_premake => 3  -- 3ヶ月分を事前に作成
);

-- 古いパーティションの自動削除（12ヶ月保持）
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

-- Bad：大量DELETEはDead Tupleを生成しVACUUM負荷を引き起こす
DELETE FROM events WHERE created_at < '2025-01-01';

-- Good：パーティションをまるごと分離して削除 - 即座に完了、VACUUM不要
ALTER TABLE events DETACH PARTITION events_2024_12;
DROP TABLE events_2024_12;

;; pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp_production

[pgbouncer]
;; プーリングモード：transactionが最も効率的
pool_mode = transaction

;; クライアント最大接続数
max_client_conn = 1000

;; データベースごとのサーバー接続数（実際のPostgreSQL接続）
default_pool_size = 30
min_pool_size = 10
reserve_pool_size = 5

;; アイドル接続タイムアウト
server_idle_timeout = 300

;; クライアント接続待ちキュータイムアウト
client_login_timeout = 60

;; ロギング
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

;; モニタリング用ポート
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# PgBouncerステータス確認
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

# アクティブ接続と待機状態の確認
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"

# PostgreSQL側の実際の接続数確認
psql -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';"

# postgresql.conf - スロークエリロギング
log_min_duration_statement = 500       # 500ms以上のクエリをロギング
log_statement = 'none'                  # DDLのみの場合は'ddl'
log_lock_waits = on                     # ロック待ちのロギング
deadlock_timeout = 1s                   # デッドロック検出周期

# pg_stat_statements拡張の有効化（必須）
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'all'

-- pg_stat_statements拡張のインストール
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 平均実行時間が最も長いクエリTOP 10
SELECT
  queryid,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct,
  rows,
  query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 使用されていないインデックスの検索
SELECT
  schemaname || '.' || indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS scan_count,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重複インデックスの検索
SELECT
  pg_size_pretty(SUM(pg_relation_size(idx))::bigint) AS total_size,
  (array_agg(idx))[1] AS idx1,
  (array_agg(idx))[2] AS idx2,
  (array_agg(indkey))[1] AS columns1,
  (array_agg(indkey))[2] AS columns2
FROM (
  SELECT indexrelid::regclass AS idx,
         indrelid, indkey,
         COALESCE(indexprs::text, '') AS exprs,
         COALESCE(indpred::text, '') AS preds
  FROM pg_index
) sub
GROUP BY indrelid, exprs, preds
HAVING COUNT(*) > 1;

-- 現在のロック待ち状況の確認
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query,
  blocked_activity.wait_event_type
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

# postgresql.conf - メモリ関連の核心パラメータ

# shared_buffers：総RAMの25%推奨（最大40%）
# 64GB RAMサーバー基準
shared_buffers = 16GB

# work_mem：ソート、ハッシュジョインなどに使用するセッション当たりのメモリ
# 注意：クエリごとに複数ノードでそれぞれ割り当てられるため大きくしすぎない
# （同時接続数 * ノード数）を考慮して設定
work_mem = 64MB

# maintenance_work_mem：VACUUM、CREATE INDEXなどに使用
maintenance_work_mem = 2GB

# effective_cache_size：OSキャッシュを含む総キャッシュサイズ（プランナーヒント用）
# 総RAMの50-75%
effective_cache_size = 48GB

# WAL関連設定
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

-- 1. 問題クエリを実行中のセッションを確認
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- 2. 問題クエリの強制終了（graceful）
SELECT pg_cancel_backend(12345);

-- 3. 終了しない場合は強制キル
SELECT pg_terminate_backend(12345);

-- CONCURRENTLY：テーブルロックなしでインデックス作成（プロダクション必須）
CREATE INDEX CONCURRENTLY idx_reports_user_date
  ON reports (user_id, report_date);

-- インデックス作成が失敗するとINVALID状態で残る。必ず確認！
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'reports';

# pg_repack：無停止でテーブルを再構成（短時間のロックのみ必要）
pg_repack -d myapp_production -t orders --no-superuser-check

# 特定インデックスのみ再構成
pg_repack -d myapp_production --index idx_orders_user_created

-- XID枯渇状態の確認
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  ROUND(100 * age(datfrozenxid)::numeric / 2147483647, 2) AS pct_toward_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- 長時間実行中のトランザクション確認（autovacuumブロックの原因）
SELECT
  pid,
  now() - xact_start AS xact_duration,
  now() - query_start AS query_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY xact_start ASC;

-- 問題トランザクション終了後、手動VACUUM FREEZEを実行
VACUUM FREEZE orders;

;; pgbouncer.ini - コネクション枯渇防止
query_wait_timeout = 30        ;; サーバー接続待ち最大30秒
server_idle_timeout = 60       ;; 60秒アイドルでサーバー接続を返却
server_lifetime = 3600         ;; 最大1時間後にサーバー接続を再作成
client_idle_timeout = 300      ;; 5分アイドルのクライアント接続を切断