PostgreSQL パフォーマンス最適化

PostgreSQLパフォーマンス最適化
クエリを高速化するテクニック

EXPLAIN ANALYZEによるクエリ分析、インデックスの最適な使い分け、VACUUM、パーティショニングまで、PostgreSQLのパフォーマンスを向上させるテクニックを解説します。

こんな人向けの記事です

PostgreSQLのクエリを高速化したい
インデックスの種類と使い分けを理解したい
VACUUMやパーティショニングの仕組みを知りたい

Step 1EXPLAIN ANALYZEでクエリの実行計画を確認する

パフォーマンス最適化の第一歩は、クエリがどのように実行されているかを正確に把握することです。PostgreSQLではEXPLAIN ANALYZEを使うことで、クエリの実行計画と実際の実行時間を確認できます。

EXPLAINの基本

EXPLAINはクエリの実行計画（プランナーがどのようにデータにアクセスするか）を表示します。ANALYZEを付けると実際にクエリが実行され、推定値ではなく実測値が得られます。

SQL

-- 実行計画の確認（実行はしない）
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

-- 実際に実行して実測値を取得
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

実行計画の読み方

EXPLAIN ANALYZEの出力には、クエリの各ステップ（ノード）が表示されます。各ノードの情報を正しく読み取ることが重要です。

実行計画の出力例

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders
   (cost=0.43..8.45 rows=1 width=40)
   (actual time=0.025..0.026 rows=1 loops=1)
   Index Cond: (customer_id = 100)
 Planning Time: 0.085 ms
 Execution Time: 0.045 ms

項目	説明
cost	推定コスト（開始コスト..総コスト）。単位は任意だが相対比較に使う
rows	推定行数（ANALYZE時は actual rows で実測値も表示）
width	1行あたりの推定バイト数
actual time	実測の実行時間（ミリ秒）
loops	そのノードが実行された回数
Planning Time	クエリプランの作成にかかった時間
Execution Time	クエリ全体の実行時間

主要なスキャン方式

スキャン方式	説明	パフォーマンス
Seq Scan	テーブル全体を順次読み取り	大量データでは遅い
Index Scan	インデックスを使って対象行に直接アクセス	選択性が高いクエリで高速
Index Only Scan	インデックスのみで結果を返す（テーブルアクセス不要）	最も高速
Bitmap Index Scan	インデックスでビットマップを作成し、テーブルをまとめて読む	中程度の選択性で有効

注意：EXPLAIN ANALYZEはクエリを実際に実行します。INSERT、UPDATE、DELETEに対して使う場合は、トランザクション内で実行してROLLBACKしましょう。

SQL

-- DELETE の実行計画を安全に確認する方法
BEGIN;
EXPLAIN ANALYZE DELETE FROM logs WHERE created_at < '2024-01-01';
ROLLBACK;

Step 2インデックスの種類と使い分け

PostgreSQLには複数のインデックスタイプがあり、データの特性やクエリパターンに応じて最適なものを選ぶ必要があります。

B-Tree インデックス

最も一般的なインデックスで、デフォルトで使用されます。等値検索（=）と範囲検索（<, >, BETWEEN）の両方に対応します。

SQL

-- B-Tree インデックス（デフォルト）
CREATE INDEX idx_users_email ON users (email);

-- 複合インデックス（左端の列から順に使われる）
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

-- 効果的なクエリ例
SELECT * FROM orders WHERE customer_id = 100;                        -- 使われる
SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2024-01-01'; -- 使われる
SELECT * FROM orders WHERE order_date > '2024-01-01';                -- 使われない（先頭列がない）

B-Treeインデックスの特徴

等値検索（=）と範囲検索（<, >, <=, >=, BETWEEN）に対応
ORDER BYの高速化にも使える
NULLも格納できる（IS NULL / IS NOT NULLの検索に対応）
複合インデックスは左端の列から順に使われる

Hash インデックス

等値検索（=）のみに特化したインデックスです。B-Treeより高速になる場合がありますが、範囲検索には使えません。

SQL

-- Hash インデックス
CREATE INDEX idx_users_uuid ON users USING hash (uuid);

-- 等値検索のみ有効
SELECT * FROM users WHERE uuid = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

GIN インデックス（Generalized Inverted Index）

配列、JSONB、全文検索など、1つの値に複数の要素が含まれるデータに最適です。

SQL

-- JSONB カラムへの GIN インデックス
CREATE INDEX idx_products_attributes ON products USING gin (attributes);

-- 配列カラムへの GIN インデックス
CREATE INDEX idx_articles_tags ON articles USING gin (tags);

-- 全文検索用の GIN インデックス
CREATE INDEX idx_articles_search ON articles USING gin (to_tsvector('japanese', title || ' ' || body));

-- クエリ例
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM articles WHERE tags @> ARRAY['PostgreSQL'];
SELECT * FROM articles WHERE to_tsvector('japanese', title || ' ' || body) @@ to_tsquery('パフォーマンス');

GiST インデックス（Generalized Search Tree）

地理空間データ、範囲型、近傍検索など、複雑なデータ構造に使われます。

SQL

-- 地理空間データへの GiST インデックス（PostGIS）
CREATE INDEX idx_stores_location ON stores USING gist (location);

-- 範囲型への GiST インデックス
CREATE INDEX idx_reservations_period ON reservations USING gist (period);

-- クエリ例
SELECT * FROM stores WHERE ST_DWithin(location, ST_MakePoint(139.7, 35.6)::geography, 1000);
SELECT * FROM reservations WHERE period && daterange('2024-07-01', '2024-07-31');

インデックスタイプの使い分け早見表

インデックス	最適な用途	対応する演算子
B-Tree	一般的な等値・範囲検索	=, <, >, <=, >=, BETWEEN, IN, IS NULL
Hash	等値検索のみ	=
GIN	配列・JSONB・全文検索	@>, <@, @@, ?, ?\|, ?&
GiST	地理空間・範囲型・近傍検索	&&, @>, <@, <<, >>, ~=

Step 3部分インデックスと式インデックス

通常のインデックスはテーブル全体を対象にしますが、部分インデックスと式インデックスを使うことで、より効率的なインデックスを作成できます。

部分インデックス（Partial Index）

WHERE句で条件を指定し、テーブルの一部の行だけにインデックスを作成します。インデックスサイズが小さくなり、更新コストも下がります。

SQL

-- アクティブなユーザーのみインデックスを作成
CREATE INDEX idx_users_active_email ON users (email)
  WHERE is_active = true;

-- 未処理の注文のみインデックスを作成
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

-- NULLでない値のみインデックスを作成
CREATE INDEX idx_users_phone ON users (phone)
  WHERE phone IS NOT NULL;

部分インデックスが効果的なケース

特定のステータスの行だけを頻繁に検索する場合
テーブルの大部分がNULLで、NULL以外の行だけを検索する場合
論理削除（is_deleted）で削除済みデータが大量にある場合

SQL

-- 部分インデックスが使われるクエリ
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-06-01';

-- 結果例:
-- Index Scan using idx_orders_pending on orders
--   (cost=0.29..8.30 rows=1 width=64)
--   (actual time=0.012..0.013 rows=3 loops=1)
--   Index Cond: (created_at > '2024-06-01')

-- 注意: WHERE句がインデックスの条件と一致しないと使われない
-- 以下のクエリではこのインデックスは使われない
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2024-06-01';

式インデックス（Expression Index）

カラムの値そのものではなく、計算式や関数の結果に対してインデックスを作成します。

SQL

-- 大文字小文字を無視した検索用
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- 日付の年月で検索する場合
CREATE INDEX idx_orders_year_month ON orders (date_trunc('month', order_date));

-- JSONBの特定キーで検索する場合
CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));

-- 式インデックスが使われるクエリ
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
SELECT * FROM orders WHERE date_trunc('month', order_date) = '2024-07-01';

注意：式インデックスは、クエリのWHERE句でまったく同じ式を使わないと効果がありません。LOWER(email) のインデックスに対して email = 'user@example.com' と書いてもインデックスは使われません。

カバリングインデックス（INCLUDE句）

PostgreSQL 11以降では、INCLUDE句を使ってインデックスに追加の列を含めることで、Index Only Scanを実現できます。

SQL

-- カバリングインデックス
CREATE INDEX idx_orders_customer_include ON orders (customer_id)
  INCLUDE (order_date, total_amount);

-- Index Only Scan が可能になるクエリ
SELECT order_date, total_amount FROM orders WHERE customer_id = 100;

-- テーブル本体へのアクセスが不要になるため高速

Step 4VACUUMとANALYZE

PostgreSQLはMVCC（多版型同時実行制御）を採用しており、UPDATEやDELETEで古い行バージョン（dead tuple）が蓄積します。VACUUMはこれを回収し、ANALYZEは統計情報を更新します。

VACUUMの仕組み

SQL

-- 通常の VACUUM（領域を再利用可能にする）
VACUUM orders;

-- VACUUM FULL（テーブルを書き直してディスク領域を返却）
-- 注意: テーブルロックがかかる
VACUUM FULL orders;

-- VACUUM VERBOSE（詳細を表示）
VACUUM VERBOSE orders;

-- 特定テーブルの dead tuple 数を確認
SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

コマンド	動作	ロック	用途
VACUUM	dead tupleを回収し再利用可能にする	読み書き可能	通常のメンテナンス
VACUUM FULL	テーブルを完全に書き直す	排他ロック（読み書き不可）	大量削除後のディスク回収
VACUUM ANALYZE	VACUUM + 統計情報更新	読み書き可能	推奨されるメンテナンス

ANALYZEの重要性

ANALYZEはテーブルの統計情報を更新します。プランナーはこの統計情報を基に最適な実行計画を選択するため、統計情報が古いとパフォーマンスが低下します。

SQL

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

-- 全テーブルの統計情報を更新
ANALYZE;

-- 統計情報の詳細を確認
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

autovacuumの設定

PostgreSQLにはautovacuumデーモンが標準で有効になっています。通常は自動で動作しますが、大量のデータ更新がある場合はテーブル単位で調整が必要です。

SQL

-- autovacuum の設定を確認
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- 特定テーブルで autovacuum を積極的に動かす設定
ALTER TABLE orders SET (
  autovacuum_vacuum_threshold = 50,        -- dead tuple がこの数を超えたら実行
  autovacuum_vacuum_scale_factor = 0.05,   -- テーブルの5%が更新されたら実行
  autovacuum_analyze_threshold = 50,
  autovacuum_analyze_scale_factor = 0.05
);

autovacuumのデフォルト設定

autovacuum_vacuum_threshold: 50（dead tupleの最小数）
autovacuum_vacuum_scale_factor: 0.2（テーブルの20%が更新されたら実行）
大きなテーブルでは scale_factor を小さくすると効果的
autovacuumを無効にするのは非推奨（トランザクションIDの周回問題が発生する）

注意：VACUUM FULLは排他ロックがかかるため、本番環境では営業時間外に実行してください。通常のVACUUMは読み書きと並行して実行できます。

Step 5接続プーリング（pgbouncer）

PostgreSQLは接続ごとにプロセスをforkするため、大量の接続はメモリとCPUを消費します。pgbouncerを使って接続をプーリングすることで、リソースの使用を大幅に削減できます。

なぜ接続プーリングが必要か

項目	プーリングなし	pgbouncer使用
同時接続100の場合のプロセス数	100プロセス	10〜20プロセス
メモリ使用量（接続あたり）	約10MB	約2KB（pgbouncer側）
接続確立の時間	100〜200ms	1〜2ms

pgbouncerの設定

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 = 200
min_pool_size = 5

; タイムアウト
server_idle_timeout = 600
client_idle_timeout = 0

プーリングモードの選択

モード	動作	制約
session	クライアントが切断するまで同じサーバー接続を保持	制約なし（効果は限定的）
transaction	トランザクション単位で接続を割り当て	PREPARE、SET、LISTENが使えない
statement	SQL文ごとに接続を割り当て	トランザクションが使えない

Djangoでの設定

pgbouncerを使う場合、DjangoのDATABASES設定でポートをpgbouncerのポート（6432）に変更します。transactionモードの場合はDISABLE_SERVER_SIDE_CURSORS = Trueも設定してください。

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': '127.0.0.1',
        'PORT': '6432',  # pgbouncer のポート
        'DISABLE_SERVER_SIDE_CURSORS': True,  # transaction モード時に必要
    }
}

Step 6パーティショニング

パーティショニングは大きなテーブルを小さなテーブル（パーティション）に分割する機能です。大量データを扱うテーブルで、検索やメンテナンスのパフォーマンスを大幅に向上させます。

パーティショニングの種類

種類	分割基準	適用例
RANGE	値の範囲	日付、ID範囲
LIST	値のリスト	ステータス、地域
HASH	ハッシュ値	均等分割

RANGEパーティショニング（日付による分割）

SQL

-- パーティションテーブルの作成
CREATE TABLE logs (
    id         BIGSERIAL,
    message    TEXT NOT NULL,
    level      VARCHAR(10) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 月別パーティションの作成
CREATE TABLE logs_2024_07 PARTITION OF logs
    FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');

CREATE TABLE logs_2024_08 PARTITION OF logs
    FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');

CREATE TABLE logs_2024_09 PARTITION OF logs
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');

-- デフォルトパーティション（どのパーティションにも該当しないデータ用）
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

LISTパーティショニング

SQL

-- 地域別にパーティションを分割
CREATE TABLE sales (
    id         BIGSERIAL,
    amount     NUMERIC NOT NULL,
    region     VARCHAR(20) NOT NULL,
    sold_at    TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE sales_east PARTITION OF sales
    FOR VALUES IN ('tokyo', 'osaka', 'nagoya');

CREATE TABLE sales_west PARTITION OF sales
    FOR VALUES IN ('fukuoka', 'hiroshima', 'kumamoto');

CREATE TABLE sales_other PARTITION OF sales DEFAULT;

パーティションプルーニング

クエリのWHERE句にパーティションキーの条件が含まれると、PostgreSQLは不要なパーティションを自動的にスキップします。これをパーティションプルーニングと呼びます。

SQL

-- 2024年8月のログだけを検索（他の月のパーティションはスキャンされない）
EXPLAIN ANALYZE
SELECT * FROM logs WHERE created_at >= '2024-08-01' AND created_at < '2024-09-01';

-- 実行計画例:
--  Seq Scan on logs_2024_08 logs
--    (cost=0.00..25.00 rows=6 width=48)
--    (actual time=0.010..0.012 rows=6 loops=1)
--    Filter: ((created_at >= ...) AND (created_at < ...))

-- パーティションプルーニングが有効か確認
SHOW enable_partition_pruning;  -- デフォルトは on

古いパーティションの管理

SQL

-- パーティションの切り離し（データは保持、親テーブルからは見えなくなる）
ALTER TABLE logs DETACH PARTITION logs_2024_07;

-- 切り離したパーティションを削除
DROP TABLE logs_2024_07;

-- 新しいパーティションの追加
CREATE TABLE logs_2024_10 PARTITION OF logs
    FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');

パーティショニングが効果的なケース

テーブルのサイズが数百万行以上
クエリが常に特定の範囲（日付など）で絞り込まれる
古いデータの削除が定期的に必要（DETACH + DROP で高速削除）
パーティション単位でのバックアップやメンテナンスが必要

注意：パーティショニングはテーブル設計の初期段階で導入するのが理想です。既存の大きなテーブルを後からパーティション化するには、データの移行作業が必要になります。

まとめパフォーマンス最適化チェックリスト

EXPLAIN ANALYZEで遅いクエリの実行計画を確認する
Seq Scanが出ている場合、適切なインデックスを検討する
データの特性に合ったインデックスタイプ（B-Tree, Hash, GIN, GiST）を選ぶ
特定条件でしか使わないインデックスは部分インデックスにする
関数を使う検索には式インデックスを作成する
dead tupleの蓄積をpg_stat_user_tablesで定期的に確認する
autovacuumの設定を更新頻度の高いテーブルに合わせて調整する
接続数が多い場合はpgbouncerの導入を検討する
大量データのテーブルにはパーティショニングを検討する

PostgreSQLパフォーマンス最適化クエリを高速化するテクニック