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

先月、午前2時にSlackメッセージが届きました。「ダッシュボードがタイムアウトしている。」プロダクションサーバーにSSHで入り、pg_stat_activityを開くと、12件の結果を返すために1,400万行をスキャンしている単一のクエリが見つかりました。修正は作成に30秒かかる部分インデックスでした。クエリは4.2秒から0.3ミリ秒になりました。

PostgreSQLのパフォーマンスとはそういうものです。問題がエキゾチックであることはほとんどありません。見落としたインデックス、古い統計情報、テーブルが1万行だったときは理にかなっていたが今は1,000万行になっているクエリ。修正は通常シンプルです。どこを見ればいいかがわかれば。

この記事は、プロダクションデータベースの運用から学んだPostgreSQLクエリ最適化のすべてです。実践を伴わない理論はありません。ここでのすべてのテクニックは、実際のシステムで実際の時間を節約してくれたものです。

デバッグの心構え：推測せず、計測せよ#

遅いクエリに対して開発者がする最大の間違いは、推測することです。「たぶんそのカラムにインデックスが必要」「たぶんJOINが遅い」「たぶんRAMを増やすべき」

推測をやめましょう。PostgreSQLは最高のクエリアナライザーの1つを持っています。使いましょう。

EXPLAIN — 設計図#

通常のEXPLAINは、実際にクエリを実行せずにPostgreSQLが計画していることを表示します：

sql

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

これはプラン（インデックススキャン）と推定コストを教えてくれます。しかしこれは予測です。PostgreSQLは行数について完全に間違っている可能性があります。推定が「1行」なのに実際の結果が50,000行だったことを見たことがあります。そのような誤推定はひどいプラン選択の連鎖を引き起こします。

通常のEXPLAINは、実際にクエリを実行せずにプランをざっと見たいときに使います。これはデータを変更するDELETEを分析する場合や、実行に30分かかるクエリの場合に重要です。

EXPLAIN ANALYZE — 真実#

sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

ここで実際の実行時間と実際の行数が見えます。本当のデバッグはここから始まります。rows=1（推定）とrows=1（実際）を比較してみてください。一致していますので、プランナーは正しい判断をしました。一致しないとき、それが最初の手がかりです。

注意：EXPLAIN ANALYZEは実際にクエリを実行します。UPDATEやDELETEを分析する場合は、トランザクションで囲みましょう：

sql

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

EXPLAIN (ANALYZE, BUFFERS) — 全体像#

これが実際に90%の時間で使っているものです：

sql

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';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

BUFFERSの出力が重要です。shared hit=312は312ページがバッファキャッシュ（RAM）から来たことを意味します。read=45は45ページをディスクから読む必要があったことを意味します。hitに対してreadが多い場合、shared_buffersが小さすぎるか、テーブルがキャッシュに収まるには大きすぎる可能性があります。

また、プランをexplain.dalibo.comに貼り付けて可視化する必要があるときはFORMAT JSONを使います。ツリービューにすると、複雑なプランがはるかに読みやすくなります：

sql

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

実際に使うEXPLAINオプション#

遅いクエリの調査で最初に実行する完全な診断クエリはこれです：

sql

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

TIMING — ノードごとのタイミング（ANALYZEではデフォルトでオンですが、明示的にします）
VERBOSE — 出力カラムリストとスキーマ修飾テーブル名を表示

探偵のようにクエリプランを読む#

すべてのクエリプランはツリーです。PostgreSQLはボトムアップで読みます。最も深くインデントされたノードが最初に実行され、結果を上に送ります。スキャンタイプの理解は他のすべての基盤です。

Seq Scan — フルテーブルスキャン#

Seq Scan on orders  (cost=0.00..25000.00 rows=1000000 width=128)

シーケンシャルスキャンはテーブル内のすべての行を読みます。100万行のテーブルなら、ディスク上のすべてのページを読みます。

しかしニュアンスがあります。Seq Scanが常に悪いわけではありません。 テーブルの30%以上を選択する場合、シーケンシャルI/Oはランダムi/Oよりもはるかに高速なので、シーケンシャルスキャンは実際にインデックススキャンより速くなります。PostgreSQLはこれを知っています。インデックスがあるのにSeq Scanを選択している場合は、WHERE句が実際にどの程度選択的かを確認しましょう。

問題になる場合：大きなテーブルからごく一部の行を選択していて、インデックスがないとき。

Index Scan — 対象を絞った検索#

Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=5 width=128)
  Index Cond: (customer_id = 42)

B-treeインデックスに行き、一致するエントリを見つけ、テーブルヒープから実際の行をフェッチします。各行のフェッチはヒープへのランダムI/O操作です。高度に選択的なクエリには優れていますが、各ヒープフェッチにはコストがかかります。

Index Only Scan — 至高の到達点#

Index Only Scan using idx_orders_customer_total on orders  (cost=0.43..4.15 rows=5 width=12)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

これが最速のスキャンタイプです。PostgreSQLはインデックスだけから必要なすべてを取得し、テーブルヒープに触れません。SELECTとWHEREの列がすべてインデックスに含まれているときに見られます。

注意点：Heap Fetches: 0はビジビリティマップが最新であることを意味します。テーブルに多くのデッドタプルがある場合（最近vacuumされていない）、PostgreSQLは行の可視性を確認するためにヒープをチェックする必要があります。これが、autovacuumがディスク容量だけでなくパフォーマンスにも重要な理由の1つです。

Bitmap Scan — 中間地点#

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

ビットマップスキャンは「インデックススキャンには多すぎ、シーケンシャルスキャンには少なすぎる」問題に対するPostgreSQLの回答です。一致する行を含むページのビットマップを構築し、物理的な位置でソートしてから順番にフェッチします。ランダムI/Oをシーケンシャルi/Oに変換します。

2つ以上のインデックスが組み合わされているときによくビットマップスキャンが見られます：

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

このBitmapAndは2つの別々のインデックスを組み合わせています。PostgreSQLが「複合インデックスはないが、これら2つの単一カラムインデックスを組み合わせられる」と言っています。動作しますが、適切な複合インデックスの方が速いでしょう。

悪い推定を見つける#

クエリプランで最も注目するのは、推定の不一致です：

Nested Loop  (cost=0.43..50.12 rows=1 width=128)
             (actual time=0.025..4521.337 rows=48753 loops=1)

プランナーは1行と推定しました。実際の結果は48,753行です。5桁の誤推定です。プランナーは1行に対するJOINだと思ってNested Loopを選択しました。48,753行なら、Hash Joinの方が桁違いに高速だったでしょう。

悪い推定の一般的な原因：

古い統計情報：テーブルでANALYZEを実行する
相関カラム：プランナーはカラム値が独立していると仮定します。status = 'shipped'とcreated_at > '2026-01-01'が相関している場合（最近の注文のほとんどが出荷済み）、プランナーは結合選択率を過小評価します
WHEREのカスタム関数：プランナーは関数を分析できないときデフォルトの選択率推定（通常、等値で0.5%、範囲で33%）を使用します
ジェネリックプランのパラメータ化クエリ：5回実行後、PostgreSQLは実際のパラメータ値を考慮しないジェネリックプランに切り替えることがあります

悪い推定を見つけたら、修正は通常次のいずれかです：ANALYZEの実行、拡張統計の作成、クエリの書き換え、またはCTEを最適化フェンスとして使用。

sql

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

インデックス戦略：適材適所のインデックス#

インデックスは無料ではありません。各インデックスは書き込みを遅くし、ディスク容量を消費し、メンテナンスが必要です。目標は「すべてにインデックスを張る」ではなく、「必要なものに正確にインデックスを張る」です。

B-tree — デフォルトの主力#

B-treeはデフォルトで、大多数のケースを処理します。等値演算子と範囲演算子（=、<、>、<=、>=、BETWEEN、IN、IS NULL）をサポートします。

sql

CREATE INDEX idx_orders_created_at ON orders (created_at);

B-treeインデックスの複合インデックスにおけるカラムの順序は極めて重要です。最左のカラムが主ソートで、次が副ソート、というように続きます。(a, b, c)のインデックスは以下を効率的に処理できます：

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 5
WHERE a = 1 ORDER BY b

以下は効率的に処理できません：

WHERE b = 2（最初のカラムをスキップ）
WHERE c = 3（最初の2カラムをスキップ）
WHERE a = 1 AND c = 3（中間のギャップ — a条件は使用されるが、c条件にはフィルタが必要）

電話帳を姓でソートし、次に名でソートしていると考えてください。すべての「Smith」エントリ、あるいは「Smith, John」を素早く見つけられます。しかし、全体をスキャンせずにすべての「John」エントリを素早く見つけることはできません。

ルール：等値条件を最初に、次に範囲条件、最後にソートカラムを配置します。

sql

-- クエリ: 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);

INCLUDEによるカバリングインデックス#

PostgreSQL 11以降では、INCLUDEでインデックスに非キーカラムを追加できます。これらのカラムはリーフページに格納されますが、B-tree構造の一部ではありません。ツリーを肥大化させずにインデックスオンリースキャンを可能にします：

sql

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

これでPostgreSQLはインデックスだけでクエリ全体を満たせます。INCLUDEがなければ、ヒープからtotalとstatusをフェッチする必要があります。コールドキャッシュでの差は劇的になりえます。INCLUDEカラムを追加しただけで、50msから0.2msにクエリが改善したのを見たことがあります。

部分インデックス — 重要なものだけインデックスする#

これは私のPostgreSQLで一番好きな機能であり、ほとんどの開発者が知らない機能です。

sql

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

このインデックスはcreated_atのフルインデックスと比較して非常に小さいです。status = 'pending'の行だけを含みます。WHERE句に一致するクエリはこの小さく高速なインデックスを使います。一致しないクエリはこれを無視します。

プロダクションでの実例：5,000万行のsessionsテーブルがありました。アクティブ（期限切れでない）なのは約20万行だけでした。user_idのフルインデックスは1.2 GBでした。WHERE expires_at > now()の部分インデックスは8 MBでした。インデックス全体がキャッシュに収まるため、クエリは12msから0.1msになりました。

sql

-- もう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;

最後のものは非常に便利です。アクティブユーザーに対するユニークなメール制約を持ちつつ、削除されたレコードで同じメールを許可できます。

GINインデックス — 全文検索とJSONB#

GIN（Generalized Inverted Index）は、値の中を検索する必要があるときの回答です。配列、JSONBドキュメント、全文検索ベクトル。

sql

-- 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));

GINインデックスは大きくビルドに時間がかかりますが、クエリは非常に高速です。複雑な包含クエリを伴う読み取り重視のワークロードには価値があります。

JSONBには、@>包含クエリに対してより小さく高速なjsonb_path_opsもありますが、存在演算子（?、?|、?&）はサポートしません：

sql

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

GiSTインデックス — 幾何型と範囲型#

GiST（Generalized Search Tree）は重複するデータ型を処理します。幾何学的図形、範囲、全文検索（GINの代替、より小さいが遅い）。

sql

-- 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');

Hashインデックス — ニッチだが有用#

Hashインデックスは等値比較にのみ有用です。PostgreSQL 10以降、WALログ記録されクラッシュセーフです。幅広のカラムでB-treeより小さく、純粋な等値ではわずかに速いです：

sql

-- 大きなテキストカラムの等値のみの検索に良い
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%';

実際にはhashインデックスをほとんど使いません。B-treeは等値も問題なく処理し、範囲クエリやソートができないことがhashインデックスをほとんどの実用的なケースで柔軟性に欠けるものにしています。

インデックスが害になるとき#

すべてのインデックスにはコストがあります：

書き込み増幅：各INSERTがテーブル上のすべてのインデックスを更新します。8つのインデックスを持つテーブルは、INSERTごとに8つの追加書き込みを意味します
HOT更新のブロック：Heap-Only Tuple（HOT）更新は、インデックス付きのカラムが変更されていない場合にのみPostgreSQLがインデックスを更新せずに行を更新できる主要な最適化です。インデックスが多い = HOT更新がブロックされる可能性が高い
Vacuumのオーバーヘッド：インデックスが多いとvacuumに時間がかかる
プランナーのオーバーヘッド：インデックスが多いとプランナーが評価するオプションが増える

プロダクションテーブルのインデックスを定期的に監査しています：

sql

-- 未使用のインデックスを見つける
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;

1回限りのマイグレーション中に作成され二度と使われなかった数ギガバイトのインデックスを見つけたことがあります。それらを削除すると書き込みが目に見えて高速化しました。

sql

-- 重複インデックスを見つける（同じカラム、同じ順序）
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;

N+1クエリ問題#

すべてのORM開発者がいつかはぶつかります。私がデバッグする中で最も一般的なパフォーマンス問題です。

N+1の見え方#

python

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

これが生成するもの：

sql

-- クエリ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;

1つか2つではなく501のクエリ。各クエリは個別には高速で、おそらく0.5ms。しかし501個を合計するとデータベース時間だけで250msになり、加えて各クエリのネットワークラウンドトリップレイテンシがあります。

ログでN+1を検出する#

N+1クエリをキャッチする最速の方法は、一時的にステートメントログを有効にすることです：

sql

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

ログを見てください。N+1は紛れもありません。異なるパラメータ値を持つ同一のクエリが立て続けに数百個見えます。

開発環境でのより対象を絞ったアプローチ：

sql

-- 遅いクエリの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;

これは100ms以上のすべてのクエリについて、関数内のクエリを含む完全なEXPLAIN ANALYZEの出力をログに記録します。

修正：Eager LoadingまたはJOIN#

ORMアプローチ — 関連データを事前にロードするようORMに指示：

python

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

生SQLアプローチ — JOINを使うだけ：

sql

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と複数クエリの比較#

ここには正当な議論があります。2つのクエリの方がJOINより良い場合もあります：

sql

-- アプローチ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);

JOINアプローチはアイテムごとに注文データを複製します。各注文に20個のアイテムがあり、注文行が幅広い場合、20倍のデータ転送になります。2クエリアプローチは各注文を正確に1回送信します。

私のルール：1対1のリレーションシップにはJOINを使い、「1」側が幅広い場合の1対多には別々のクエリを検討する。 ただし常にベンチマークを。2番目のクエリのネットワークラウンドトリップコストは、重複データよりも高いことが多いです。

一般的なクエリの書き換え#

クエリが遅い理由はインデックスの欠如ではなく、書き方にあることもあります。PostgreSQLのプランナーは優秀ですが、魔法ではありません。

サブクエリ vs JOIN vs CTE#

これら3つのアプローチは非常に異なるプランを生成する可能性があります：

sql

-- 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;

PostgreSQL 12以降では、CTEは通常インライン化されます（プランナーがサブクエリとして扱います）ので、パフォーマンスは同一です。しかしPostgreSQL 11以下では、CTEは最適化フェンスです。プランナーがマテリアライズし、述語をプッシュスルーできません。まだPG 11を使っている場合、パフォーマンスクリティカルなクエリではCTEを避けましょう。

EXISTS vs IN vs JOIN#

これは常に出てくる問題です：

sql

-- 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;

大きなテーブルでは、EXISTSはショートサーキットするためよく勝ちます。INバージョンはフィルタリング前にreturnのorder_idの完全なリストを構築する必要があります。JOINバージョンは重複を生成する可能性があり、ソートやハッシュステップを追加するDISTINCTが必要になります。

私のデフォルト：関連行の存在をチェックする場合はEXISTSを使用。 最も意味的に明確で、通常最速です。

ただし反例もあります。サブクエリの結果セットが小さく、複数の条件に必要な場合：

sql

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

SELECT *の回避#

これは単なるコードスタイルの好みではありません。実際のパフォーマンスに影響があります：

sql

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

SELECT *を使うと：

インデックスオンリースキャンが使えない（すべてのカラムがインデックスに必要）
ネットワーク上でより多くのデータを転送
ソートとハッシュにより多くのメモリを使用
誰かが後で10MBのBYTEAカラムを追加した場合、既存のクエリが静かに遅くなる

ウィンドウ関数 vs サブクエリ#

ウィンドウ関数はPostgreSQLの最も強力な機能の1つで、相関サブクエリよりもほぼ常に高パフォーマンスです：

sql

-- 遅い: 相関サブクエリが行ごとに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';

もう1つの一般的なパターン：グループごとの最新行の取得：

sql

-- 遅い: 相関サブクエリ
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;

DISTINCT ONはこのパターンでの私の定番です。簡潔で読みやすく、PostgreSQLは適切なインデックスでうまく最適化します：

sql

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

正しいページネーション#

OFFSETは大きなデータセットでの罠です：

sql

-- ページ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;

修正はキーセットページネーション（カーソルベースのページネーションとも呼ばれる）です：

sql

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

これは常に高速です。なぜなら、どの「ページ」にいるかに関係なく、インデックスを使って正しい位置に直接ジャンプするからです。トレードオフは任意のページ番号にジャンプできないことですが、無限スクロールや「次のページ」UIには、キーセットページネーションが厳密に優れています。

複雑なソート順の場合：

sql

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

これは行値比較を使用しており、PostgreSQLは(price, id)の複合インデックスで効率的に処理します。

テーブル統計とバキューム#

PostgreSQLのクエリプランナーはデータに関する統計情報に基づいて判断を下します。悪い統計は悪いプランにつながります。それだけのことです。

ANALYZE：統計情報の更新#

sql

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

correlation値は特に興味深いです。-1から1の範囲で、行の物理的な順序がカラムの論理的な順序とどの程度一致しているかを測定します。1または-1に近い相関は、データがそのカラムで物理的にソートされていることを意味し、範囲スキャンが非常に効率的（シーケンシャルI/O）になります。0に近い相関は、範囲クエリでランダムI/Oを意味します。

sql

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

デフォルトの統計ターゲットは100で、PostgreSQLは300 * 100 = 30,000行をサンプリングします。多くのユニーク値や偏った分布を持つカラムでは、これを500や1000に増やすと、ANALYZE時間がわずかに長くなる代わりにプランナーにより良いデータを提供します。

オートバキュームのチューニング#

オートバキュームは2つのことを行います。デッドタプル（削除または更新された行）からスペースを回収し、統計を更新します。忙しいテーブルでは、デフォルトのオートバキューム設定は保守的すぎることがよくあります。

sql

-- オートバキュームのステータスを確認
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;

数百万のデッドタプルがあり、最後のバキュームが数時間前のテーブルが見えたら、オートバキュームが遅れています。

高チャーンテーブル（セッション、ジョブキュー、メトリクスなど）には、テーブルごとのオートバキューム設定を設定します：

sql

-- 書き込みの多いテーブル用のより積極的なオートバキューム
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）
);

テーブルの肥大化#

PostgreSQLが行を更新するとき、行をその場で修正しません。新しいバージョンを作成し、古いものをデッドとしてマークします。バキュームはデッド行を回収しますが、スペースはそのテーブルでのみ再利用されます。ディスク上のテーブルファイルは縮小しません。

時間が経つと、頻繁に更新されるテーブルは著しく肥大化する可能性があります。ライブデータが必要とするよりもディスク上のテーブルがはるかに大きくなります。これはスキャンするページが増え、I/Oが増え、キャッシュ圧力が増すことを意味します。

sql

-- テーブルの肥大化を推定（簡略版）
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;

深刻に肥大化したテーブルには、最終手段としてVACUUM FULLがあります。テーブル全体を書き換えます。しかし排他ロックを取るため、ダウンタイムなしでライブプロダクションテーブルには実行できません。より良いアプローチはpg_repackで、ロックなしで同じことを行います：

bash

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

コネクションプーリング#

多くの開発者を驚かせることがあります。PostgreSQLのコネクションは高コストです。各コネクションは新しいプロセスを生成し（スレッドではなく）、約5-10 MBのメモリを消費し、無視できないforkオーバーヘッドがあります。

デフォルトのmax_connectionsは100です。アプリケーションサーバーが20ワーカーで各5コネクションを開くと、すでに上限に達しています。バックグラウンドジョブプロセッサ、モニタリングツール、どこかで実行中のマイグレーションを追加すると、トラブルです。

なぜコネクションプーラーが必要か#

プーラーなしでは、アプリケーションが500の同時リクエストを処理する必要がある場合、500のPostgreSQLコネクションが必要です。コネクションオーバーヘッドだけで5 GBのメモリになり、PostgreSQLのパフォーマンスはプロセス管理オーバーヘッドのため数百コネクションを超えると大幅に低下します。

PgBouncerをPostgreSQLの前に置けば、500のアプリケーションコネクションがおそらく20の実際のPostgreSQLコネクションにマッピングされます。プーラーはすべてのデータベースコネクションが使用中のときにリクエストをキューに入れます。

PgBouncerの設定#

ini

[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

トランザクション vs セッションモード#

トランザクションモード（pool_mode = transaction）：各トランザクション後にコネクションがプールに返されます。95%の場合でこれが必要です。最大のコネクション再利用。
セッションモード（pool_mode = session）：クライアントセッション全体でコネクションが保持されます。プリペアドステートメント、SETコマンド、LISTEN/NOTIFY、またはその他のセッションレベルの機能が必要な場合にこれを使用します。
ステートメントモード（pool_mode = statement）：各ステートメント後にコネクションが返されます。ほとんどのアプリケーションには制約が厳しすぎます。明示的なトランザクションすら使えません。

トランザクションモードの注意点：プリペアドステートメント（セッションレベルの状態）を使えません、セッション変数のSETを使えません、LISTEN/NOTIFYが期待通りに動作しません。ほとんどのORMにはプリペアドステートメントを無効にする方法があります。

Node.jsのpgドライバーでの場合：

javascript

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,
});

プールサイジングの公式#

最適なPostgreSQLコネクションプールサイズの一般的な公式があります：

optimal_connections = (core_count * 2) + effective_spindle_count

4コアとSSD（有効スピンドル数1）のモダンサーバーの場合：

optimal = (4 * 2) + 1 = 9

これは直感に反して低く見えます。しかしPostgreSQLはほとんどのモダンハードウェアでCPUバウンドです（SSDは十分高速でI/Oがボトルネックになることはめったにありません）。これを超えるコネクションは、実際にスローダウンを引き起こすコンテキストスイッチングオーバーヘッドにつながります。

実際には、バーストトラフィックを処理するためにdefault_pool_sizeをこの数値の2-3倍に設定します。ピーク負荷時にはクエリの一部がPgBouncerキューで待機し、すべてが同時にPostgreSQLを直撃しないという理解のもとで。

実践的チェックリスト：すべての遅いクエリに対する正確な手順#

「このクエリが遅い」という報告を受けたときの実際のプロセスです。毎回この順序でこの手順に従います。

ステップ1：実際のクエリを取得する#

「エンドポイントが遅い」ではなく、実際のSQLです。ORMを使っている場合は、クエリログを有効にします：

sql

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

またはpg_stat_statementsで合計時間によるトップクエリを確認します：

sql

-- 合計実行時間によるトップ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;

pg_stat_statementsはPostgreSQLパフォーマンスにとって最も価値のある拡張機能です。実行していない場合は、今すぐインストールしてください：

sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

ステップ2：EXPLAIN (ANALYZE, BUFFERS)を実行#

sql

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

注目する点：

悪い行推定 — 実際の行数が推定行数と大きく異なる
大きなテーブルのSeq Scan — インデックスが欠けている可能性
多くの行を持つNested Loop — Hash JoinまたはMerge Joinであるべき
高いバッファリード — コールドキャッシュまたはテーブルが大きすぎる
ディスクにスピルするソート操作 — work_memを増やすかソート用インデックスを追加

ステップ3：テーブル統計を確認#

sql

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

last_analyzeが古いか、n_dead_tupがn_live_tupに対して高い場合：

sql

ANALYZE orders;

その後、EXPLAIN ANALYZEを再実行します。プランが変わったら、古い統計が問題でした。

ステップ4：既存のインデックスを確認#

sql

-- このテーブルにどんなインデックスがある？
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;

インデックスは存在するが使われていないかもしれません。カラムの順序がクエリに対して間違っているかもしれません。

ステップ5：インデックスを作成または変更#

クエリプランに基づいて、適切なインデックスを作成します。前後でEXPLAIN ANALYZEでテストします。

sql

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

プロダクションテーブルでは常にCONCURRENTLYを使用してください。通常のCREATE INDEXはすべての書き込みをブロックするフルテーブルロックを取得します。

ステップ6：クエリの書き換えを検討#

インデックスが存在し統計が新鮮だがクエリがまだ遅い場合、クエリ自体を見てください：

サブクエリをJOINに書き換えられるか？
OFFSETが問題を引き起こしているか？キーセットページネーションに切り替え
必要以上にカラムを選択していないか？
相関サブクエリをウィンドウ関数にできるか？
CTEがプランナーの最適化を妨げていないか？

ステップ7：サーバー設定を確認#

特定のクエリパターンでは、サーバー設定が重要です：

sql

-- 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;

work_memの変更でソートのディスクスピル問題が解決した場合、グローバルに増やすことを検討してください。ただし注意してください。操作ごとであり、コネクションごとではありません。10のソート操作を持つ複雑なクエリでwork_mem = 256MBだと、単一クエリで2.5 GBを使用する可能性があります。

ステップ8：修正後の監視#

修正して忘れないでください。修正が維持されていることを確認しましょう：

sql

-- 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;

ボーナス：すべての新規データベースに適用するクイックウィン#

これは、パフォーマンスの問題が発生する前にセットアップするすべてのPostgreSQLデータベースに適用する設定とプラクティスです：

sql

-- 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にはリスタートが必要

そして毎週実行する監視クエリ：

sql

-- 注意が必要なテーブル
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;

まとめ#

PostgreSQLクエリ最適化は暗黒の魔術ではありません。体系的なプロセスです：

計測する — 推測しない。EXPLAIN (ANALYZE, BUFFERS)があなたの最良の友。
プランを理解する — スキャンタイプ、結合タイプ、行推定の読み方を学ぶ。
戦略的にインデックスを張る — 正しいカラムに正しい順序で正しいインデックスを。部分インデックスとカバリングインデックスは過小評価されている超能力。
より良いクエリを書く — 存在チェックにはINよりEXISTS、OFFSETよりキーセットページネーション、相関サブクエリよりウィンドウ関数。
データベースをメンテナンスする — オートバキュームのチューニング、統計の更新、コネクションプーリング。
継続的に監視する — pg_stat_statementsがデータベースの時間の使われ方を教えてくれる。定期的にチェック。

4秒かかるクエリと0.3ミリ秒のクエリの違いは、ハードウェアであることはめったにありません。ほぼ常に知識です。どこを見て何を変えるかを知ること。そして今、あなたはどこを見るべきかを知っています。