PostgreSQLクエリ最適化：数秒からサブミリ秒へ

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

-- 相関カラムの拡張統計を作成
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

CREATE INDEX idx_orders_created_at ON orders (created_at);

-- クエリ: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- 良い: 等値が最初、次に範囲、次にソート
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- 悪い: 範囲が等値の前
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

-- 頻繁に実行するクエリ:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

-- ordersの2%だけが'pending'だが、常にクエリしている
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- もう1つの一般的なパターン: ソフトデリート
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- アクティブレコードのみのユニーク制約
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

-- JSONB包含クエリ
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- これが高速になる:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- 全文検索
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

-- IP範囲検索
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- 時間範囲（スケジュール競合）
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- 重複する予約を検索:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

-- 大きなテキストカラムの等値のみの検索に良い
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- これは高速:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- これはhashインデックスを使用できない:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

-- 未使用のインデックスを見つける
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 indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- プライマリキーとユニーク制約を除外
  )
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重複インデックスを見つける（同じカラム、同じ順序）
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

# Python / SQLAlchemy — 定番のトラップ
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # 各注文ごとに別のクエリが発生
    print(f"Customer: {order.customer.name}")

-- クエリ1: 保留中のすべての注文を取得
SELECT * FROM orders WHERE status = 'pending';
 
-- クエリ2: 注文1の顧客を取得
SELECT * FROM customers WHERE id = 101;
 
-- クエリ3: 注文2の顧客を取得
SELECT * FROM customers WHERE id = 102;
 
-- ...クエリ502: 注文500の顧客を取得
SELECT * FROM customers WHERE id = 600;

-- 0ms以上のすべてのクエリをログ（つまり、すべてのクエリ）
SET log_min_duration_statement = 0;
 
-- プロダクション向けにはより良い: 遅いクエリだけをログ
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms閾値
SELECT pg_reload_conf();

-- 遅いクエリのauto_explainを有効化
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

# SQLAlchemy — joinedloadは同じクエリで顧客をフェッチ
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

-- アプローチ1: 単一JOIN（一対多の場合重複データが発生する可能性）
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- アプローチ2: 2つのクエリ（注文のカラムが多い場合データ転送が少ない）
SELECT * FROM orders WHERE status = 'pending';
-- アプリケーションがorder_ids = [1, 2, 3, ...]を収集
SELECT * FROM order_items WHERE order_id = ANY($1);

-- WHERE内のサブクエリ — 多くの場合問題ないが、時にひどい
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — 通常最良の選択
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — PG 11以下ではマテリアライズ、PG 12+では最適化
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

-- EXISTS — 最初のマッチで停止、存在チェックに最適
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- INとサブクエリ — 完全な結果セットを最初に構築
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — returnsが注文ごとに複数行ある場合重複が発生
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

-- サブクエリが少数の行を返す場合、INは完全に問題ない
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

-- 悪い: キロバイト単位のTEXTカラムを含む25カラムすべてをフェッチ
SELECT * FROM products WHERE category = 'electronics';
 
-- 良い: 必要なものだけフェッチ
SELECT id, name, price FROM products WHERE category = 'electronics';

-- 遅い: 相関サブクエリが行ごとに1回実行
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- 速い: ウィンドウ関数が単一パスで計算
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

-- 遅い: 相関サブクエリ
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- 速い: DISTINCT ON（PostgreSQL固有、非常に便利）
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- これも速い: ROW_NUMBERウィンドウ関数
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

-- ページ1: 高速
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- ページ500: 遅い — PostgreSQLは9,980行をスキャンして破棄する必要がある
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- ページ5000: 非常に遅い — 99,980行をスキャンして20行を返す
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

-- 最初のページ
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- 次のページ: 前のページの最後のIDを使用
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

-- 複数のソートカラムによるキーセットページネーション
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

-- 単一テーブルの分析
ANALYZE orders;
 
-- データベース全体の分析
ANALYZE;
 
-- カラムの現在の統計を確認
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- 偏った分布のカラムの統計ターゲットを増やす
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- オートバキュームのステータスを確認
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 書き込みの多いテーブル用のより積極的なオートバキューム
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- デッドタプル1%でバキューム（デフォルト20%）
  autovacuum_analyze_scale_factor = 0.005,   -- 変更0.5%で分析（デフォルト10%）
  autovacuum_vacuum_cost_delay = 2           -- より高速なバキューム（新しいPGではデフォルト2ms）
);

-- テーブルの肥大化を推定（簡略版）
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

pg_repack --table orders --no-kill-backend -d mydb

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; プールモード
pool_mode = transaction
 
; プールサイジング
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; タイムアウト
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // PgBouncerのポート、PostgreSQLのポートではない
  database: "mydb",
  max: 20, // PgBouncerのdefault_pool_sizeと一致
  statement_timeout: 30000,
  // PgBouncerのトランザクションモード用にプリペアドステートメントを無効化
  prepare: false,
});

-- 100ms以上のすべてのクエリを一時的にログ
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

-- 合計実行時間によるトップ10クエリ
SELECT
  queryid,
  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 pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <遅いクエリ>;

-- 統計はいつ最後に更新された？
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

ANALYZE orders;

-- このテーブルにどんなインデックスがある？
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

-- インデックスをCONCURRENTLYで作成（テーブルをロックしない）
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- インデックスが使われていることを確認
EXPLAIN (ANALYZE, BUFFERS) <遅いクエリ>;

-- work_mem: ソートとハッシュ結合用のメモリ（操作ごと、コネクションごとではない）
-- デフォルトは4MBで、複雑なクエリには低すぎる
SET work_mem = '64MB';  -- これを試してEXPLAIN ANALYZEを再実行
 
-- effective_cache_size: プランナーにどれだけのディスクキャッシュを期待するか伝える
-- RAMの約75%に設定
SHOW effective_cache_size;
 
-- random_page_cost: ランダムI/OとシーケンシャルI/Oの比率
-- デフォルトは4.0、SSD用に1.1-1.5に設定
SHOW random_page_cost;

-- pg_stat_statementsをリセットして変更後のフレッシュデータを確認
SELECT pg_stat_statements_reset();
 
-- 1時間/1日後に確認
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

-- 1. pg_stat_statementsのインストール
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. 遅いクエリログの有効化（50msが私の閾値）
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD対応の設定
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. 貧弱なデフォルトからwork_memを増やす
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. effective_cache_sizeをRAMの75%に設定
-- 16GBサーバーの場合:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. shared_buffersをRAMの25%に設定
-- 16GBサーバーの場合:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. オートバキュームの応答性を向上
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- 変更を適用
SELECT pg_reload_conf();
-- 注: shared_buffersにはリスタートが必要

-- 注意が必要なテーブル
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;