PostgreSQLクエリチューニング実践 - 遅いSQLを速くする

はじめに

「本番環境でクエリが遅くなった」「ユーザーから画面表示が重いと報告された」という経験はありませんか。PostgreSQLを使ったシステム開発において、クエリパフォーマンスの問題は避けて通れない課題です。

単にインデックスを追加するだけでは解決しないケースも多く、N+1問題、不要なソートやスキャン、非効率なJOIN順序、古い統計情報など、様々な要因が複合的に絡み合っています。本記事では、これらの問題を体系的に理解し、実践的なクエリチューニング手法を身につけることを目的としています。

本記事で扱うトピックは以下の通りです。

N+1問題の発見と解消方法（JOINによる一括取得、LATERAL JOINの活用）
不要なソート・スキャンの排除（インデックスを活用したソート回避、カバリングインデックス）
適切なJOIN順序の設定（プランナへのヒント、join_collapse_limit）
統計情報（ANALYZE）の更新と効果
クエリリライトのテクニック（EXISTS vs IN、サブクエリの最適化、CTEの注意点）

この記事を読むことで、実行計画を読み解きながらボトルネックを特定し、具体的な改善策を実装できるようになります。

前提条件

PostgreSQL 14以降がインストールされていること
EXPLAIN ANALYZEの基本的な読み方を理解していること
インデックスの基本概念を理解していること

EXPLAIN ANALYZEやインデックスの基礎については、以下の関連記事を参照してください。

サンプルデータの準備

本記事で使用するサンプルテーブルとデータを準備します。ECサイトを想定した構成で、実際のパフォーマンス問題を再現しやすいデータ量を用意しています。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94


-- ユーザーテーブル（10万件）
CREATE TABLE users (
    user_id       SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) UNIQUE NOT NULL,
    prefecture    VARCHAR(20) NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- カテゴリテーブル（100件）
CREATE TABLE categories (
    category_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    parent_id     INTEGER REFERENCES categories(category_id)
);

-- 商品テーブル（10万件）
CREATE TABLE products (
    product_id    SERIAL PRIMARY KEY,
    name          VARCHAR(200) NOT NULL,
    category_id   INTEGER REFERENCES categories(category_id),
    price         INTEGER NOT NULL,
    stock         INTEGER DEFAULT 0,
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 注文テーブル（50万件）
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    user_id       INTEGER REFERENCES users(user_id),
    order_date    DATE NOT NULL,
    status        VARCHAR(20) NOT NULL,
    total_amount  INTEGER NOT NULL
);

-- 注文明細テーブル（100万件）
CREATE TABLE order_items (
    item_id       SERIAL PRIMARY KEY,
    order_id      INTEGER REFERENCES orders(order_id),
    product_id    INTEGER REFERENCES products(product_id),
    quantity      INTEGER NOT NULL,
    unit_price    INTEGER NOT NULL
);

-- カテゴリデータの生成
INSERT INTO categories (name, parent_id)
SELECT
    'カテゴリ' || i,
    CASE WHEN i <= 10 THEN NULL ELSE 1 + ((i - 11) % 10) END
FROM generate_series(1, 100) AS i;

-- ユーザーデータの生成
INSERT INTO users (name, email, prefecture)
SELECT
    'ユーザー' || i,
    'user' || i || '@example.com',
    (ARRAY['東京都', '大阪府', '愛知県', '福岡県', '北海道', '神奈川県', '埼玉県', '千葉県'])[1 + (i % 8)]
FROM generate_series(1, 100000) AS i;

-- 商品データの生成
INSERT INTO products (name, category_id, price, stock, is_active)
SELECT
    '商品' || i,
    1 + (i % 100),
    1000 + (i % 50) * 100,
    (i % 100),
    i % 20 != 0
FROM generate_series(1, 100000) AS i;

-- 注文データの生成
INSERT INTO orders (user_id, order_date, status, total_amount)
SELECT
    1 + (i % 100000),
    DATE '2024-01-01' + (i % 365),
    (ARRAY['pending', 'confirmed', 'shipped', 'delivered', 'cancelled'])[1 + (i % 5)],
    1000 + (i % 100) * 500
FROM generate_series(1, 500000) AS i;

-- 注文明細データの生成
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    1 + (i % 500000),
    1 + (i % 100000),
    1 + (i % 5),
    1000 + (i % 50) * 100
FROM generate_series(1, 1000000) AS i;

-- 統計情報の更新
ANALYZE users;
ANALYZE categories;
ANALYZE products;
ANALYZE orders;
ANALYZE order_items;

N+1問題の発見と解消

N+1問題とは

N+1問題とは、親レコードを1回のクエリで取得し、その後各親レコードに対して子レコードを個別に取得するクエリがN回発生するパフォーマンス問題です。ORMを使ったアプリケーション開発でよく発生しますが、手書きSQLでも同様の問題が起こりえます。

flowchart LR
    subgraph n1["N+1問題（非効率）"]
        A1["1. 注文一覧取得<br/>SELECT * FROM orders"] --> B1["2. 注文1の明細取得"]
        A1 --> B2["3. 注文2の明細取得"]
        A1 --> B3["4. 注文3の明細取得"]
        A1 --> B4["..."]
        A1 --> B5["N+1. 注文Nの明細取得"]
    end

N+1問題の具体例

以下は、注文一覧を取得し、それぞれの注文に対して明細を取得するという典型的なN+1パターンです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- 1回目: 注文一覧を取得
SELECT order_id, user_id, order_date, status, total_amount
FROM orders
WHERE order_date >= '2024-12-01'
  AND order_date < '2025-01-01'
ORDER BY order_date DESC
LIMIT 100;

-- 2回目以降: 各注文の明細を取得（これがN回繰り返される）
SELECT oi.item_id, oi.product_id, oi.quantity, oi.unit_price, p.name AS product_name
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_id = 1;  -- order_idが1〜100まで繰り返し

100件の注文を取得すると、合計101回のクエリが発行されます。1回のクエリが10msだとしても、合計で1秒以上かかってしまいます。

JOINによる一括取得

N+1問題の最もシンプルな解決策は、JOINを使って1回のクエリで必要なデータをすべて取得することです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


-- JOINで一括取得
SELECT 
    o.order_id,
    o.user_id,
    o.order_date,
    o.status,
    o.total_amount,
    oi.item_id,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    p.name AS product_name
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-12-01'
  AND o.order_date < '2025-01-01'
ORDER BY o.order_date DESC, o.order_id, oi.item_id;

実行計画を確認してみましょう。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


EXPLAIN ANALYZE
SELECT 
    o.order_id,
    o.user_id,
    o.order_date,
    o.status,
    o.total_amount,
    oi.item_id,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    p.name AS product_name
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-12-01'
  AND o.order_date < '2025-01-01'
ORDER BY o.order_date DESC, o.order_id, oi.item_id;

JOINによる一括取得では、データベースへのラウンドトリップが1回で済むため、ネットワークオーバーヘッドが大幅に削減されます。

LATERAL JOINの活用

「各注文について、最新の3件の明細だけを取得したい」といったケースでは、通常のJOINでは対応が難しくなります。このような「各行に対して関連するN件を取得する」パターンでは、LATERAL JOINが有効です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


-- 各注文の最新3件の明細を取得
SELECT 
    o.order_id,
    o.order_date,
    o.status,
    latest_items.item_id,
    latest_items.product_name,
    latest_items.quantity
FROM orders o
CROSS JOIN LATERAL (
    SELECT 
        oi.item_id,
        p.name AS product_name,
        oi.quantity
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE oi.order_id = o.order_id
    ORDER BY oi.item_id DESC
    LIMIT 3
) AS latest_items
WHERE o.order_date >= '2024-12-01'
  AND o.order_date < '2025-01-01'
ORDER BY o.order_id, latest_items.item_id;

LATERAL JOINでは、外部クエリの各行に対してサブクエリが実行されます。一見N+1と同じように見えますが、これは1つのSQLクエリ内で完結するため、データベース内部で最適化され、ラウンドトリップは1回で済みます。

集約によるN+1回避

明細の合計や件数だけが必要な場合は、集約関数を使うことでさらに効率化できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


-- 各注文の明細件数と合計金額を一括取得
SELECT 
    o.order_id,
    o.order_date,
    o.status,
    COUNT(oi.item_id) AS item_count,
    SUM(oi.quantity * oi.unit_price) AS calculated_total
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-12-01'
  AND o.order_date < '2025-01-01'
GROUP BY o.order_id, o.order_date, o.status
ORDER BY o.order_date DESC;

不要なソート・スキャンの排除

ソートがボトルネックになるケース

大量のデータをソートする処理は、メモリを大量に消費し、ディスクへのスピル（一時ファイルへの書き出し）が発生すると急激にパフォーマンスが低下します。

1
2
3
4
5
6


-- インデックスなしの状態でのソート
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'delivered'
ORDER BY order_date DESC
LIMIT 100;

実行結果の例：

Limit  (cost=25432.12..25432.37 rows=100 width=44) (actual time=234.567..234.589 rows=100 loops=1)
  ->  Sort  (cost=25432.12..25682.12 rows=100000 width=44) (actual time=234.564..234.578 rows=100 loops=1)
        Sort Key: order_date DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Seq Scan on orders  (cost=0.00..12500.00 rows=100000 width=44) (actual time=0.015..156.234 rows=100000 loops=1)
              Filter: (status = 'delivered'::text)
              Rows Removed by Filter: 400000
Planning Time: 0.123 ms
Execution Time: 234.678 ms

インデックスを活用したソート回避

ORDER BYで使用するカラムにインデックスがあれば、ソート処理をスキップできます。

1
2
3
4
5
6
7
8
9


-- 複合インデックスの作成
CREATE INDEX idx_orders_status_date ON orders (status, order_date DESC);

-- 再度実行
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'delivered'
ORDER BY order_date DESC
LIMIT 100;

実行結果の例：

Limit  (cost=0.42..12.45 rows=100 width=44) (actual time=0.034..0.156 rows=100 loops=1)
  ->  Index Scan using idx_orders_status_date on orders  (cost=0.42..12045.42 rows=100000 width=44) (actual time=0.032..0.145 rows=100 loops=1)
        Index Cond: (status = 'delivered'::text)
Planning Time: 0.234 ms
Execution Time: 0.189 ms

Sortノードが消え、Index Scanだけで結果が得られるようになりました。実行時間が約1200倍改善されています。

flowchart TB
    subgraph before["改善前"]
        A1["Seq Scan<br/>50万行スキャン"] --> B1["Filter<br/>10万行に絞り込み"]
        B1 --> C1["Sort<br/>10万行ソート"]
        C1 --> D1["Limit<br/>100行取得"]
    end
    
    subgraph after["改善後"]
        A2["Index Scan<br/>ソート済みで取得"] --> D2["Limit<br/>100行で停止"]
    end

カバリングインデックス（Index Only Scan）

クエリで取得するカラムがすべてインデックスに含まれている場合、テーブル本体にアクセスせずにインデックスだけで結果を返せます。これをIndex Only Scanと呼びます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- カバリングインデックスの作成
CREATE INDEX idx_orders_covering ON orders (status, order_date DESC) 
INCLUDE (user_id, total_amount);

-- 必要なカラムだけを取得
EXPLAIN ANALYZE
SELECT order_id, user_id, order_date, total_amount
FROM orders
WHERE status = 'delivered'
ORDER BY order_date DESC
LIMIT 100;

実行結果の例：

Limit  (cost=0.42..8.67 rows=100 width=20) (actual time=0.028..0.098 rows=100 loops=1)
  ->  Index Only Scan using idx_orders_covering on orders  (cost=0.42..8245.42 rows=100000 width=20) (actual time=0.026..0.089 rows=100 loops=1)
        Index Cond: (status = 'delivered'::text)
        Heap Fetches: 0
Planning Time: 0.156 ms
Execution Time: 0.123 ms

Heap Fetches: 0は、テーブル本体へのアクセスが発生していないことを示しています。

不要なDISTINCTの排除

DISTINCTは内部でソートまたはハッシュ処理を行うため、本当に必要な場合のみ使用すべきです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 非効率: 不要なDISTINCT
SELECT DISTINCT user_id FROM orders WHERE order_date >= '2024-12-01';

-- 効率的: EXISTS を使用
SELECT user_id FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.user_id 
      AND o.order_date >= '2024-12-01'
);

適切なJOIN順序の設定

PostgreSQLのJOIN順序最適化

PostgreSQLのプランナは、JOINの順序を自動的に最適化します。しかし、テーブル数が多くなると、すべての組み合わせを検討するのは現実的でなくなります。

PostgreSQLでは、join_collapse_limitとfrom_collapse_limitというパラメータでこの動作を制御しています。

1
2
3


-- 現在の設定を確認
SHOW join_collapse_limit;  -- デフォルト: 8
SHOW from_collapse_limit;  -- デフォルト: 8

テーブル数がこの値を超えると、プランナはJOIN順序の最適化を諦め、クエリに記述された順序でJOINを実行します。

JOIN順序が重要な理由

JOINの順序によって、中間結果のサイズが大きく変わります。中間結果が大きいと、その後のJOIN処理も遅くなります。

1
2
3
4
5
6
7
8
9


-- 非効率な順序: 大きなテーブル同士を先にJOIN
EXPLAIN ANALYZE
SELECT o.order_id, u.name, p.name AS product_name
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN users u ON o.user_id = u.user_id
WHERE u.prefecture = '東京都'
  AND p.category_id = 1;

1
2
3
4
5
6
7
8
9


-- 効率的な順序: 絞り込みが効くテーブルを先にJOIN
EXPLAIN ANALYZE
SELECT o.order_id, u.name, p.name AS product_name
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE u.prefecture = '東京都'
  AND p.category_id = 1;

明示的なJOIN順序の指定

プランナに特定のJOIN順序を強制したい場合は、join_collapse_limitを1に設定します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- セッション単位でJOIN順序の最適化を無効化
SET join_collapse_limit = 1;

-- この状態では、クエリに記述した順序でJOINが実行される
EXPLAIN ANALYZE
SELECT o.order_id, u.name, p.name AS product_name
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE u.prefecture = '東京都'
  AND p.category_id = 1;

-- 設定を元に戻す
RESET join_collapse_limit;

JOINアルゴリズムの選択

PostgreSQLは、データの特性に応じて以下のJOINアルゴリズムを自動選択します。

アルゴリズム	特徴	適したケース
Nested Loop	外側の各行に対して内側をスキャン	内側が少量、またはインデックスが使える場合
Hash Join	片方をハッシュテーブル化して結合	等価結合で、片方が小さい場合
Merge Join	両方をソートして突き合わせ	両方がソート済み、または範囲結合

特定のアルゴリズムを無効化してパフォーマンスを比較することもできます。

1
2
3
4
5
6
7
8
9


-- Hash Joinを無効化してNested Loopを強制
SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_hashjoin;

-- Nested Loopを無効化
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_nestloop;

統計情報（ANALYZE）の更新

統計情報とは

PostgreSQLのプランナは、テーブルの統計情報を基に最適な実行計画を選択します。統計情報には以下のような内容が含まれます。

テーブルの総行数
各カラムのNULLの割合
各カラムの値の分布（最頻値、ヒストグラム）
各カラムのユニーク値の数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 統計情報の確認
SELECT 
    schemaname,
    tablename,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE tablename IN ('users', 'orders', 'order_items', 'products');

統計情報が古いと起こる問題

統計情報が実際のデータと乖離していると、プランナが誤った実行計画を選択します。

1
2
3


-- 推定行数と実際の行数の乖離を確認
EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date >= '2024-12-01';

出力例（統計情報が古い場合）：

Seq Scan on orders  (cost=0.00..12500.00 rows=1000 width=44) (actual time=0.015..123.456 rows=42000 loops=1)
  Filter: (order_date >= '2024-12-01'::date)

rows=1000（推定）に対してrows=42000（実測）と大きな乖離があります。この乖離により、インデックスを使うべきところでSeq Scanが選択されるなどの問題が発生します。

ANALYZEコマンドの実行

統計情報を更新するには、ANALYZEコマンドを実行します。

1
2
3
4
5
6
7
8


-- 特定のテーブルの統計情報を更新
ANALYZE orders;

-- 特定のカラムのみ更新
ANALYZE orders (order_date, status);

-- データベース全体の統計情報を更新
ANALYZE;

自動バキュームと自動ANALYZE

PostgreSQLには、自動的にANALYZEを実行する機能（autovacuum）があります。

1
2
3
4


-- autovacuumの設定を確認
SHOW autovacuum;
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;

自動ANALYZEは、以下の条件で実行されます。

変更行数 > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * テーブル行数

デフォルトでは、テーブルの10%以上が変更されると自動ANALYZEが実行されます。

統計情報のサンプリング精度

デフォルトでは、統計情報は100行のサンプルから生成されます。カーディナリティが高いカラムでは、サンプル数を増やすことで精度が向上します。

1
2
3
4
5


-- 特定カラムの統計情報サンプル数を増やす
ALTER TABLE orders ALTER COLUMN order_date SET STATISTICS 500;

-- 統計情報を再生成
ANALYZE orders;

クエリリライトのテクニック

EXISTS vs IN

サブクエリを使う場合、EXISTSとINのどちらを使うかでパフォーマンスが変わることがあります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- INを使用（サブクエリの結果セットが大きい場合に非効率）
EXPLAIN ANALYZE
SELECT * FROM users u
WHERE u.user_id IN (
    SELECT o.user_id FROM orders o WHERE o.order_date >= '2024-12-01'
);

-- EXISTSを使用（通常はこちらが効率的）
EXPLAIN ANALYZE
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.user_id 
      AND o.order_date >= '2024-12-01'
);

EXISTSは、条件を満たす行が1つ見つかった時点で探索を終了するため、多くの場合INより効率的です。

NOT IN vs NOT EXISTS

NOT INには特別な注意が必要です。サブクエリの結果にNULLが含まれると、NOT INは常にFALSEを返します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


-- NOT IN（NULLがあると問題、かつ非効率）
SELECT * FROM users u
WHERE u.user_id NOT IN (
    SELECT o.user_id FROM orders o WHERE o.order_date >= '2024-12-01'
);

-- NOT EXISTS（NULLセーフで効率的）
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.user_id 
      AND o.order_date >= '2024-12-01'
);

-- LEFT JOIN + IS NULL（同等の結果）
SELECT u.* FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.order_date >= '2024-12-01'
WHERE o.order_id IS NULL;

サブクエリの最適化

相関サブクエリは、外部クエリの各行に対して実行されるため、非効率になることがあります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


-- 非効率: 相関サブクエリ
SELECT 
    u.user_id,
    u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) AS order_count,
    (SELECT MAX(order_date) FROM orders o WHERE o.user_id = u.user_id) AS last_order_date
FROM users u
WHERE u.prefecture = '東京都';

-- 効率的: JOINと集約
SELECT 
    u.user_id,
    u.name,
    COUNT(o.order_id) AS order_count,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.prefecture = '東京都'
GROUP BY u.user_id, u.name;

CTEの最適化バリアに注意

PostgreSQL 12以降では、CTEはデフォルトでインライン化されますが、MATERIALIZEDを指定すると強制的に中間結果が実体化されます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


-- インライン化される（最適化される）
WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date >= '2024-12-01'
)
SELECT u.name, COUNT(ro.order_id)
FROM users u
JOIN recent_orders ro ON u.user_id = ro.user_id
GROUP BY u.name;

-- 強制的に実体化（最適化バリアになる）
WITH recent_orders AS MATERIALIZED (
    SELECT * FROM orders WHERE order_date >= '2024-12-01'
)
SELECT u.name, COUNT(ro.order_id)
FROM users u
JOIN recent_orders ro ON u.user_id = ro.user_id
GROUP BY u.name;

CTEの実体化は、同じCTEを複数回参照する場合や、複雑なクエリを段階的にデバッグする場合に有用ですが、パフォーマンス上の理由でMATERIALIZEDを使う場合は効果を検証してください。

UNION vs UNION ALL

UNIONは重複排除のためにソートまたはハッシュ処理を行います。重複がないことが明らかな場合は、UNION ALLを使用してください。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


-- 非効率: 重複排除処理が発生
SELECT user_id, name FROM users WHERE prefecture = '東京都'
UNION
SELECT user_id, name FROM users WHERE prefecture = '大阪府';

-- 効率的: 重複排除なし
SELECT user_id, name FROM users WHERE prefecture = '東京都'
UNION ALL
SELECT user_id, name FROM users WHERE prefecture = '大阪府';

-- さらに効率的: OR条件で1回のスキャン
SELECT user_id, name FROM users WHERE prefecture IN ('東京都', '大阪府');

CASE式による条件分岐の最適化

複数のクエリを条件で分岐する代わりに、CASE式を使って1回のクエリにまとめられることがあります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 非効率: 複数回のクエリ
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'confirmed';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';

-- 効率的: 1回のクエリで集計
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
    COUNT(CASE WHEN status = 'confirmed' THEN 1 END) AS confirmed_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_count
FROM orders;

パフォーマンス改善のチェックリスト

クエリチューニングを行う際のチェックリストをまとめます。

実行計画の確認

EXPLAIN ANALYZEで実行計画を確認したか
推定行数と実際の行数に大きな乖離がないか
Seq Scanが大きなテーブルに対して実行されていないか
Sortノードがボトルネックになっていないか

インデックスの確認

WHERE句、JOIN条件、ORDER BY句のカラムにインデックスがあるか
複合インデックスのカラム順序は適切か
カバリングインデックスで対応できないか
使われていないインデックスがないか

クエリの見直し

N+1問題が発生していないか
不要なDISTINCT、ORDER BY、サブクエリがないか
EXISTSとINの選択は適切か
CTEが最適化バリアになっていないか

統計情報の確認

ANALYZEが最近実行されているか
autovacuumが正常に動作しているか
統計情報のサンプル数は十分か

まとめ

本記事では、PostgreSQLで遅いSQLを速くするための実践的なクエリチューニング手法を解説しました。

N+1問題はJOINやLATERAL JOINで一括取得することで解消できます。不要なソートはインデックスを活用することで回避でき、カバリングインデックスを使えばテーブルアクセスも削減できます。JOIN順序はプランナが最適化しますが、テーブル数が多い場合は明示的な指定が必要になることもあります。

統計情報はプランナの判断根拠となるため、定期的なANALYZEの実行が重要です。クエリリライトでは、EXISTS vs IN、サブクエリの最適化、CTEの扱いなど、細かなテクニックの積み重ねがパフォーマンス改善につながります。

クエリチューニングは、EXPLAIN ANALYZEで実行計画を確認し、ボトルネックを特定し、改善策を実装し、再度効果を検証するというサイクルの繰り返しです。本記事で紹介した手法を活用して、効率的なクエリを実装してください。

はじめに#

前提条件#

サンプルデータの準備#

N+1問題の発見と解消#

N+1問題とは#

N+1問題の具体例#

JOINによる一括取得#

LATERAL JOINの活用#

集約によるN+1回避#

不要なソート・スキャンの排除#

ソートがボトルネックになるケース#

インデックスを活用したソート回避#

カバリングインデックス（Index Only Scan）#

不要なDISTINCTの排除#

適切なJOIN順序の設定#

PostgreSQLのJOIN順序最適化#

JOIN順序が重要な理由#

明示的なJOIN順序の指定#

JOINアルゴリズムの選択#

統計情報（ANALYZE）の更新#

統計情報とは#

統計情報が古いと起こる問題#

ANALYZEコマンドの実行#

自動バキュームと自動ANALYZE#

統計情報のサンプリング精度#

クエリリライトのテクニック#

EXISTS vs IN#

NOT IN vs NOT EXISTS#

サブクエリの最適化#

CTEの最適化バリアに注意#

UNION vs UNION ALL#

CASE式による条件分岐の最適化#

パフォーマンス改善のチェックリスト#

実行計画の確認#

インデックスの確認#

クエリの見直し#

統計情報の確認#

まとめ#

参考リンク#

はじめに

前提条件

サンプルデータの準備

N+1問題の発見と解消

N+1問題とは

N+1問題の具体例

JOINによる一括取得

LATERAL JOINの活用

集約によるN+1回避

不要なソート・スキャンの排除

ソートがボトルネックになるケース

インデックスを活用したソート回避

カバリングインデックス（Index Only Scan）

不要なDISTINCTの排除

適切なJOIN順序の設定

PostgreSQLのJOIN順序最適化

JOIN順序が重要な理由

明示的なJOIN順序の指定

JOINアルゴリズムの選択

統計情報（ANALYZE）の更新

統計情報とは

統計情報が古いと起こる問題

ANALYZEコマンドの実行

自動バキュームと自動ANALYZE

統計情報のサンプリング精度

クエリリライトのテクニック

EXISTS vs IN

NOT IN vs NOT EXISTS

サブクエリの最適化

CTEの最適化バリアに注意

UNION vs UNION ALL

CASE式による条件分岐の最適化

パフォーマンス改善のチェックリスト

実行計画の確認

インデックスの確認

クエリの見直し

統計情報の確認

まとめ

参考リンク