PostgreSQLのANALYZEで実行計画のずれを直す方法｜プランナ統計の更新とdefault_statistics_targetの調整

この記事のポイント

・ANALYZE でプランナ統計を更新すればプラン選択ミスを直せる
・EXPLAIN ANALYZE の推定行数と実測行数のずれが診断の入口
・default_statistics_target を上げると統計精度が向上する
・列ごとに統計精度を個別調整する ALTER TABLE SET STATISTICS も有効

-- 実行計画と実際の行数を同時に取得
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..45230.00 rows=1 width=128)
                    (actual time=0.042..312.451 rows=89234 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 910766
Planning Time: 0.085 ms
Execution Time: 337.201 ms

-- psql でデータベースに接続した状態で実行
ANALYZE;
-- または VERBOSE で更新されたテーブルを確認
ANALYZE VERBOSE;

INFO:  analyzing "public.orders"
INFO:  "orders": scanned 30000 of 110453 pages, containing 2000000 live rows and
       0 dead rows; 30000 rows in sample, 7364044 estimated total rows
ANALYZE

# 特定テーブルのみ
ANALYZE orders;

# 特定テーブルの特定列だけ
ANALYZE orders (customer_id, order_date);

# スキーマを指定する場合
ANALYZE sales.orders;

ANALYZE orders;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
  (cost=0.56..8432.12 rows=87510 width=128)
  (actual time=0.021..18.432 rows=89234 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.210 ms
Execution Time: 22.840 ms

SHOW default_statistics_target;

 default_statistics_target
---------------------------
 100
(1 row)

# /etc/postgresql/16/main/postgresql.conf （Ubuntu系）
# または /var/lib/pgsql/16/data/postgresql.conf （RHEL系）

default_statistics_target = 200

# postgresql.conf の変更を再起動なしで反映
SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

ANALYZE VERBOSE;

# customer_id 列だけ統計精度を 500 に引き上げる
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;

# 変更を反映するために ANALYZE を再実行
ANALYZE orders;

# デフォルトに戻す
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS -1;

-- 統計の n_distinct を確認する
SELECT
    attname,
    n_distinct,
    null_frac,
    array_length(most_common_vals::text::text[], 1) AS mcv_count
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY attname;

SELECT
    schemaname,
    relname AS tablename,
    last_autoanalyze,
    last_analyze,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_autoanalyze NULLS FIRST;

 schemaname | tablename | last_autoanalyze           | last_analyze               | n_live_tup | n_dead_tup
------------+-----------+----------------------------+----------------------------+------------+------------
 public     | orders    | 2026-09-09 03:15:42.123456 | 2026-09-10 09:30:12.654321 |    1000000 |       2344
 public     | products  | 2026-09-10 00:05:11.000000 |                            |      50000 |        120
(2 rows)

SHOW enable_seqscan;

 enable_seqscan
----------------
 off
(1 row)

SET enable_seqscan = on;

-- region と category の組み合わせの相関統計を収集する
CREATE STATISTICS ext_region_category ON region, category FROM orders;
ANALYZE orders;

ERROR:  must be owner of table orders