PostgreSQL: PostgreSQLパフォーマンスチューニング実践

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1001;

Index Scan using idx_orders_user_id on orders  (cost=0.42..8.44 rows=1 width=72) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (user_id = 1001)
  Buffers: shared hit=4
Planning Time: 0.085 ms
Execution Time: 0.032 ms

-- statusにインデックスがないため、プランナーはSeq Scanを選択
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM orders WHERE status = 'pending';

Aggregate (actual time=45.123..45.124 rows=1 loops=1)
  -> Seq Scan on orders (actual time=0.012..42.890 rows=50000 loops=1)
       Filter: (status = 'pending'::text)
       Rows Removed by Filter: 950000

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 高選択性: プランナーはIndex Scanを使用
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id = 1001;

Index Scan using idx_orders_user_id on orders (actual time=0.015..0.018 rows=3 loops=1)
  Index Cond: (user_id = 1001)

-- 中程度の選択性: プランナーはBitmapを選択
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE user_id BETWEEN 1000 AND 1100;

Bitmap Heap Scan on orders (actual time=0.523..2.145 rows=523 loops=1)
  Recheck Cond: (user_id >= 1000 AND user_id <= 1100)
  -> Bitmap Index Scan on idx_orders_user_id (actual time=0.412..0.412 rows=523 loops=1)
       Index Cond: (user_id >= 1000 AND user_id <= 1100)

-- 小外部 + インデックス付き内部 = Nest Loop
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 1001;

-- 大外部 + 未ソート内部 = Hash Join
EXPLAIN (COSTS OFF)
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date > '2024-01-01';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- 遅いクエリ: 大テーブルへのSeq Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Seq Scan, cost=0.00..15432.00, actual time=120ms

-- インデックスを追加
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- 正確な統計のために再分析
ANALYZE orders;

-- 同じクエリが今度はIndex Scanを使用
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
-- Index Scan, cost=0.42..890.00, actual time=2ms

CREATE TABLE

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 合計時間トップ10
SELECT left(query, 80) AS query_preview,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       rows,
       round((100.0 * shared_blks_hit /
           nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

  result  
----------
   42.50
(1 row)

-- ディスク読み取りが最も多いクエリ
SELECT left(query, 80) AS query_preview,
       shared_blks_read AS disk_reads,
       shared_blks_hit  AS cache_hits,
       calls,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
WHERE shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 10;

  result  
----------
   42.50
(1 row)

# pgbouncer.ini
[databases]
shop_db = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

# 16GB RAMサーバー向けのpostgresql.confチューニング
shared_buffers = 4GB           # RAMの25%
work_mem = 64MB                # ソート操作あたりのメモリ
effective_cache_size = 12GB    # RAMの75%
maintenance_work_mem = 1GB     # VACUUM, CREATE INDEX用
effective_io_concurrency = 200 # SSDの場合。HDDは2
random_page_cost = 1.1         # SSDの場合。HDDは4.0

-- 現在の設定を確認
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

-- 実行時に変更（一部は再起動不要）
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

-- 再起動必須のパラメータ
SELECT name, setting, boot_val, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

ALTER TABLE

-- デッドタプル率を確認
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- 高書き込みテーブルではscale_factorを下げて頻繁にvacuum
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = '1ms',
    autovacuum_vacuum_cost_limit = 1000
);

-- 1000万行のordersテーブルでは、vacuumのトリガーは:
-- 1000万 × 0.05 = 50万デッドタプル（デフォルトの200万と比較）

-- SQL statement executed successfully

-- PG 12+: vacuum進捗の追跡
SELECT pid,
       relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count
FROM pg_stat_progress_vacuum;

 count 
-------
     5
(1 row)

-- 並列クエリを有効化（PG 10+）
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SET parallel_tuple_cost = 0.001;
SET min_parallel_table_scan_size = '8MB';

-- 並列計画を確認
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*), category
FROM products
GROUP BY category;

Finalize Aggregate (actual time=12.3..12.4 rows=5 loops=1)
  -> Gather (actual time=12.1..12.3 rows=15 loops=1)
       Workers Planned: 2
       Workers Launched: 2
       -> Partial Aggregate (actual time=8.5..8.6 rows=5 loops=3)
            -> Parallel Seq Scan on products (actual time=0.2..6.1 rows=33333 loops=3)

SELECT relname AS table_name,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch,
       round(100.0 * idx_scan / nullif(idx_scan + seq_scan, 0), 2) AS idx_scan_pct,
       n_live_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- アンチパターン1: OR条件がインデックス使用を妨げる
-- 悪い
SELECT * FROM orders WHERE user_id = 1001 OR status = 'pending';
-- 良い: UNION ALLを使用
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 1001;

-- アンチパターン2: 関数がインデックス付きカラムをラップ
-- 悪い
SELECT * FROM orders WHERE lower(status) = 'pending';
-- 良い
SELECT * FROM orders WHERE status = lower('PENDING');

-- アンチパターン3: 先頭ワイルドカード付きLIKE
-- 悪い
SELECT * FROM products WHERE name LIKE '%phone%';
-- 良い: pg_trgmまたは全文検索
SELECT * FROM products WHERE name % 'phone';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- CharlieのEコマースシステム向け完全なパフォーマンス最適化ワークフロー

-- ステップ1: スロークエリを特定
SELECT left(query, 60) AS q, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;

-- ステップ2: 最悪のクエリを分析
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name, p.name AS product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- ステップ3: 不足インデックスを追加
CREATE INDEX idx_orders_date_status ON orders (order_date, status);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

-- ステップ4: 統計情報を更新
ANALYZE orders;
ANALYZE order_items;
ANALYZE products;

-- ステップ5: 高書き込みテーブルのautovacuumチューニング
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

-- ステップ6: 複雑なソート用にwork_memを増加
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET effective_cache_size = '12GB';
SELECT pg_reload_conf();

-- ステップ7: 改善を確認
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND o.status = 'completed';

-- ステップ8: テーブル健全性を監視
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_items', 'products')
ORDER BY n_dead_tup DESC;