【2026年版】PostgreSQLパフォーマンスチューニング実践ガイド：クエリ最適化からインデックス戦略まで

-- postgresql.confに追加
-- shared_preload_libraries = 'pg_stat_statements'

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

-- 最も時間がかかっているクエリのTop10
SELECT
  query,
  calls,
  total_exec_time / 1000 AS total_time_sec,
  mean_exec_time AS avg_time_ms,
  rows,
  shared_blks_hit + shared_blks_read AS total_blocks,
  ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY total_amount DESC
LIMIT 20;

-- 悪い例：不要なカラムも取得
SELECT * FROM orders WHERE user_id = 123;

-- 良い例：必要なカラムのみ取得
SELECT id, amount, status, created_at
FROM orders
WHERE user_id = 123;

-- 悪い例：ユーザーごとにクエリを発行
-- アプリケーション側で for user in users: SELECT * FROM orders WHERE user_id = user.id

-- 良い例：JOINまたはサブクエリで一括取得
SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = ANY(ARRAY[1, 2, 3, 4, 5]);

-- 悪い例：関数適用でインデックスが使えない
SELECT * FROM orders WHERE DATE(created_at) = '2026-02-01';

-- 良い例：範囲条件でインデックスを活用
SELECT * FROM orders
WHERE created_at >= '2026-02-01 00:00:00'
  AND created_at < '2026-02-02 00:00:00';

-- 悪い例：OR条件でインデックスが使えないケース
SELECT * FROM products
WHERE category = 'electronics' OR price < 1000;

-- 良い例：UNIONで分割
SELECT * FROM products WHERE category = 'electronics'
UNION
SELECT * FROM products WHERE price < 1000;

-- 基本原則：選択性の高いカラムを先に配置
-- クエリ: WHERE status = 'active' AND created_at >= '2026-01-01' AND user_id = 123

-- user_idが最も選択性が高い場合
CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, created_at);

-- カバリングインデックス（Index Only Scanを実現）
CREATE INDEX idx_orders_covering
ON orders (user_id, status, created_at)
INCLUDE (amount, product_id);

-- アクティブな注文のみにインデックス
CREATE INDEX idx_orders_active
ON orders (user_id, created_at)
WHERE status = 'active';

-- 未処理タスクのみにインデックス
CREATE INDEX idx_tasks_pending
ON tasks (priority, created_at)
WHERE completed_at IS NULL;

-- メールアドレスの小文字検索用
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

-- JSONB内のフィールド検索用
CREATE INDEX idx_events_type
ON events ((metadata->>'event_type'));

-- 日付の年月検索用
CREATE INDEX idx_orders_year_month
ON orders (DATE_TRUNC('month', created_at));

-- 未使用インデックスの検出
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重複インデックスの検出
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  pg_size_pretty(pg_relation_size(a.indexrelid)) AS size1,
  pg_size_pretty(pg_relation_size(b.indexrelid)) AS size2
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid != b.indexrelid
  AND a.indkey::text LIKE b.indkey::text || '%'
WHERE a.indrelid::regclass::text NOT LIKE 'pg_%';

-- インデックスの膨張率チェック
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 現在のwork_mem設定の確認
SHOW work_mem;

-- セッション単位での一時変更（テスト用）
SET work_mem = '256MB';

-- 大量ソートが必要な特定クエリのみ一時的に増加
SET LOCAL work_mem = '512MB';
SELECT * FROM large_table ORDER BY complex_column;
RESET work_mem;

# postgresql.conf

# 接続数の設定
max_connections = 200

# 並列クエリの設定
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
parallel_tuple_cost = 0.01
parallel_setup_cost = 500

# JITコンパイルの設定
jit = on
jit_above_cost = 100000
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# プーリングモード
pool_mode = transaction

# 接続数の設定
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

# タイムアウト
server_idle_timeout = 600
client_idle_timeout = 0

-- テーブル単位のautovacuum設定
ALTER TABLE orders SET (
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_threshold = 500,
  autovacuum_analyze_scale_factor = 0.02
);

-- 高更新テーブルの設定例
ALTER TABLE sessions SET (
  autovacuum_vacuum_threshold = 100,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 10
);

-- テーブルの肥大化率を確認
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
  pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename::regclass)) AS index_size,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

-- パーティションテーブルの作成
CREATE TABLE events (
  id BIGSERIAL,
  event_type VARCHAR(50),
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL
) 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 OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
  next_month DATE := DATE_TRUNC('month', NOW()) + INTERVAL '1 month';
  partition_name TEXT;
  start_date TEXT;
  end_date TEXT;
BEGIN
  partition_name := 'events_' || TO_CHAR(next_month, 'YYYY_MM');
  start_date := TO_CHAR(next_month, 'YYYY-MM-DD');
  end_date := TO_CHAR(next_month + INTERVAL '1 month', 'YYYY-MM-DD');

  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
    partition_name, start_date, end_date
  );
END;
$$ LANGUAGE plpgsql;

-- キャッシュヒット率（95%以上が目標）
SELECT
  'buffer cache hit ratio' AS metric,
  ROUND(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS value
FROM pg_stat_database;

-- トランザクション実行状況
SELECT
  datname,
  numbackends AS active_connections,
  xact_commit AS commits,
  xact_rollback AS rollbacks,
  ROUND(100.0 * xact_rollback / NULLIF(xact_commit + xact_rollback, 0), 2) AS rollback_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- ロック待ちの検出
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
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.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

# docker-compose.yml（監視スタック）
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    environment:
      DATA_SOURCE_NAME: "postgresql://monitor:password@postgres:5432/myapp?sslmode=disable"
    ports:
      - "9187:9187"

  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"

  grafana:
    image: grafana/grafana:latest
    ports:
      - "3000:3000"
    environment:
      GF_SECURITY_ADMIN_PASSWORD: admin