PostgreSQLが遅い理由を手を動かして理解する - Docker+2000万行データで学ぶ15のチューニング実例

これまでのエンジニア生活で、こういう人に何度か出会ってきました。「列指向ストレージ(OpenSearchなど)は集計や全文検索が得意」という、ただそれだけの話のはずなのに、たまたま踏んだ1つの重いクエリだけを見て「PostgreSQLは遅い、OpenSearchは速い、だから全部OpenSearchに移そう」と言い出す人。JOINで一度詰まった経験だけで「JOINは遅い、だから正規化は悪だ」と結論づけ、なんでも非正規化してしまう人。

「そんな極端な人、本当にいるの?」と笑った人もいるかもしれません。でも本当にいます。

こういう人たちに共通しているのは、特定の1シナリオでの体験を、そのDBや設計手法そのものの評価にまで広げてしまっていることです。列指向ストレージが得意なのはあくまで特定の集計・検索パターンの話で、PostgreSQLが全般的に遅いという話ではありません。JOINが遅かったのは大抵、インデックスが無かったかクエリの書き方に問題があっただけで、正規化そのものが悪いわけでもありません。

では逆に聞きますが、皆さんは今使っているPostgreSQLを"普通に"使えているでしょうか。EXPLAINの読み方、インデックスが効く条件と効かない条件、拡張統計、パーティショニング、同時実行時のロック競合——このあたりを人に説明できるくらい理解できているか、一度立ち止まって考えてみてほしいのです。

この記事は、そういう「なんとなくの思い込み」を積み重ねるのではなく、実際に自分の手を動かして確かめた結果だけを載せるために書きました。ローカルのDockerにPostgreSQL 18を立て、Fakerで作った通販サイト風のダミーデータ(最大2000万行)を投入し、実務でよく踏む15個のパフォーマンスの罠を自分で再現しています。掲載している実行時間はすべて実測値です。「だいたいこれくらい速くなる」という体感ではなく、EXPLAIN (ANALYZE, BUFFERS)の生の出力を載せています。検証に使ったコード一式はGitHubで公開しているので、同じ環境を自分のPCでも再現できます。

対象読者は、テーブル設計・CRUD操作は理解しているけれど「なぜこのクエリだけ遅いのか」「インデックスを足せば本当に速くなるのか」が分からず苦戦しているエンジニアです。逆に言うと、PostgreSQLの内部実装に詳しい人には物足りない内容かもしれません。

検証環境:Dockerに2000万行のECデータを用意する

なぜ自分の手で確かめるのか

「インデックスを張れば速くなる」という知識は多くの人が知っています。ただ、それがどれくらい速くなるのか、なぜ速くなるのか、逆にどういう時は効かないのかは、実際に大きなデータで試してみないと体に入ってきません。数百件しかないテーブルでは、インデックスがあってもなくてもクエリは一瞬で終わるからです。

そこでこの記事では、小さな通販サイトの1〜2年分の運用データを想定した規模で検証しました。具体的には顧客30万人・商品5万点・注文500万件・注文明細1500万行・アクセスログ2000万行という、個人開発や中小企業のシステムなら十分あり得るボリュームです。

20テーブルの通販サイト風スキーマ

顧客・商品・注文だけでなく、住所やカート、クーポン、レビュー、アクセスログまで含めた20テーブルを用意しました。実務でよくある「1対多」「多対多」「自己参照(カテゴリの親子関係)」「外部キー制約のない緩い参照」といった関係を一通り含めています。

重要なのは、主キー(id)以外のインデックスは意図的に一切張っていないことです。実際の開発でも、テーブルを作った直後はこの状態がほとんどです。ここから「困った出来事」が起きるたびにインデックスや設計を直していく、という体で読み進めてください。

Fakerの「種プール増幅方式」で2000万行を高速に作る

Fakerは名前や住所をリアルに生成してくれるライブラリですが、1行ずつ愚直に呼び出すと2000万行を作るのに日が暮れます。そこで「実在感のある名前・文章のプール」を数千〜数万件だけ先にFakerで作っておき、そこから乱数でサンプリングして組み合わせることで、量とリアルさを両立させました。投入はCOPYコマンド(PostgreSQLの一括ロード機能)でストリーミングするため、2000万行でも数分で終わります。

git clone https://github.com/kkm-horikawa-blog-content/postgres-tuning-ec-lab.git
cd postgres-tuning-ec-lab
make up                   # PostgreSQL 18をDockerで起動
make schema                # 20テーブルのスキーマを作成(インデックスなし)
make load SCALE=1000000    # 種プール増幅方式でデータ投入(数分)

DBeaverで接続して、自分の目で確かめる

この記事のBefore/Afterは、DBeaver(無料で使えるDB管理GUI)を使えば誰でも同じ手順で再現できます。接続情報は以下の通りです。

• ・Host: localhost / Port: 5455
• ・Database: ectuning / User: lab / Password: lab

接続できたら、SQLエディタに各シナリオのEXPLAIN (ANALYZE, BUFFERS)付きのSQLを貼り付けて実行し、結果パネル下の「Explain plan」タブを開いてください。Seq Scan(全件読み)・Index Scan(索引経由)・Sort・Nested Loopといったノードがツリーで表示されます。インデックスを追加する前後で同じSQLをもう一度実行すれば、このツリーの形が変わる様子を自分の目で見られます。

インデックスの基本:「ないと何が起こるか」を実際に見る

シナリオ1: 外部キーにインデックスは自動で付かない

意外と知られていない事実ですが、PostgreSQLは外部キー制約を張っても、参照している側のカラムに自動でインデックスは作りません。主キー側(参照される側)には自動で索引が付きますが、外部キー側(参照する側)は自分で明示的にインデックスを作る必要があります。

「特定の顧客の購入履歴を注文明細まで含めて取得する」という、ECサイトなら必ず出てくるクエリで試してみます。

SELECT o.id AS order_id, o.ordered_at, oi.product_id, oi.quantity, oi.unit_price
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE c.email = 'user00149999@example.com';

Before(orders.customer_id / order_items.order_idにインデックスなし): 655.4ミリ秒。実行計画を見ると、たった44件の結果を返すためにParallel Seq Scan(全件読み)が500万件のordersテーブルと1500万件のorder_itemsテーブルの両方に対して発生していました。

Parallel Hash Join (actual time=150.667..618.808 rows=14.67 loops=3)
  Hash Cond: (oi.order_id = o.id)
  ->  Parallel Seq Scan on order_items oi (actual rows=4998026 loops=3)
  ->  Parallel Hash ...
        ->  Hash Join (Hash Cond: (o.customer_id = c.id))
              ->  Parallel Seq Scan on orders o (actual rows=1666666 loops=3)
Execution Time: 655.366 ms

対処は単純です。JOINで使っている外部キーカラムにインデックスを作るだけです。

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

After: 1.6ミリ秒(約400倍)。実行計画はNested Loopに変わり、顧客1件→注文11件→明細44件という、必要な分だけを索引でたどる形になりました。読み込んだページ数(バッファ)も18万ページ超から62ページまで減っています。JOINで使うカラムには、外部キー制約の有無に関わらず自分でインデックスを張る必要があります。

シナリオ2: 外部キー"制約"がなくてもインデックスは要る

シナリオ1は外部キー制約があるケースでしたが、実務では「クーポンコード」のように、正式な外部キー制約を張らずに文字列で緩やかに紐づけているカラムもよくあります。今回のデータではorder_coupons.coupon_codeがまさにそれで、coupons.codeへの参照ですが物理的な外部キー制約は付けていません。

「特定クーポンの利用実績を注文情報付きで見る」というクエリで試すと、Before: 65.7ミリ秒(50万件のorder_couponsをSeq Scan)、インデックスを1本足すとAfter: 27.8ミリ秒(約2.4倍)になりました。改善幅は他のシナリオに比べると控えめですが、これは対象が0.5万件と絞り込み後の件数がそこそこ多いためです。ポイントは「外部キー制約という仕組みの有無ではなく、JOINやWHEREで実際に使われているかどうか」でインデックスの要否を判断することです。

シナリオ3: N+1問題を、インデックスとバッチ取得の両方で解決する

Djangoのprefetch_relatedを使わずにループを書いてしまうと発生する、いわゆる「N+1問題」も実際に再現してみました。商品一覧40件それぞれについて、商品画像とレビューを1件ずつ別クエリで取りに行くパターンです。

# 擬似コード: 商品40件それぞれについて2回ずつクエリ(合計80回)
for product in products[:40]:
    images = db.query("SELECT * FROM product_images WHERE product_id = %s", product.id)
    reviews = db.query("SELECT * FROM reviews WHERE product_id = %s", product.id)

Before: 4844ミリ秒(80回の往復合計)。product_images・reviewsのどちらにもproduct_idのインデックスが無かったため、1回のクエリごとにテーブル全体(15万件・200万件)を読みに行っていました。

対処は2段構えです。まずインデックスを追加し、さらにクエリ自体を「対象の商品IDをまとめて1回で取る」形に書き換えます。

CREATE INDEX idx_product_images_product_id ON product_images (product_id);
CREATE INDEX idx_reviews_product_id ON reviews (product_id);

-- 40件まとめて2回で取得(prefetch_related相当)
SELECT * FROM product_images WHERE product_id = ANY(:product_ids);
SELECT * FROM reviews        WHERE product_id = ANY(:product_ids);

After: 63ミリ秒(約77倍、往復回数も80回→2回)。N+1問題の本質は「クエリ1回あたりの遅さ」ではなく「往復回数そのもの」にあるため、インデックスだけでは往復回数の多さは解決しません。ORMの機能(prefetch_related、includes、joinedloadなど)を正しく使う、あるいは自分でバッチ化する、という書き方の見直しとセットで考える必要があります。

シナリオ5: 複合インデックスで「絞り込み+並び替え」を1本にする

管理画面でよくある「ステータスと配送先都道府県で絞り込み、新しい順に50件表示」というクエリです。

SELECT id, customer_id, ordered_at, total_amount
FROM orders
WHERE status = 'pending' AND shipping_prefecture = '東京都'
ORDER BY ordered_at DESC
LIMIT 50;

Before: 119.8ミリ秒。WHEREの2条件に合う約5,660件をSeq Scanで拾い集めたあと、Sortノードで並び替えてから先頭50件を返していました。単一列のインデックスだけでは、絞り込みと並び替えの両方を同時に満たせません。

WHEREの列とORDER BYの列を、この順番のまま1本の複合インデックスにまとめます。

CREATE INDEX idx_orders_status_pref_orderedat
  ON orders (status, shipping_prefecture, ordered_at DESC);

After: 0.465ミリ秒(約257倍)。実行計画からSortノードが消え、インデックスをそのまま先頭から50件読むだけで完結するようになりました。複合インデックスは「等号で絞る列→範囲・並び替えで使う列」の順に並べるのがコツです。

シナリオ6: あいまい検索とpg_trgm、そして「効かない場合」

LIKE '%キーワード%'のような前方一致でない検索は、通常のB-treeインデックスが使えません。ここで使うのがpg_trgm拡張(文字列を3文字ずつの断片=トライグラムに分解して索引化する仕組み)です。実はkkm-mako.com自体も、記事本文のあいまい検索にこの仕組みを使っています。

アクセスログ2000万件からUser-Agent文字列を検索するクエリで試しました。

SELECT id FROM access_logs WHERE user_agent ILIKE '%Mozilla 42%';
-- 該当件数は全体の約1%(約20万件)

Before: 2358.9ミリ秒。全件Seq Scanでした。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_access_logs_useragent_trgm
  ON access_logs USING gin (user_agent gin_trgm_ops);

After: 1339.5ミリ秒(約1.8倍)。インデックスは使われるようになりましたが、正直に言うと劇的な差にはなりませんでした。理由は「約1%」という一致率が、GINインデックス(トライグラムの索引)にとって決して低くないからです。索引で1%分のヒントを得たとしても、結局は元の行(ヒープ)を大量に読みに行く必要があり、全件読みとの差が縮まります。実際、商品説明のようなもっと絞り込める条件(0.1%台の一致率)で試すと、Seq ScanのままBitmap Heap Scanに変わる様子が見えました。「インデックスを足せば必ず速くなる」わけではなく、選択率(全体のうちどれだけ絞り込めるか)次第で効果が変わる——これは初心者ほど見落としがちな点です。

クエリの書き方を変えるだけで速くなる

シナリオ7: OFFSETページネーションの罠とkeyset方式

一覧画面のページネーションでOFFSETを使うと、ページが深くなるほど遅くなります。理由は単純で、OFFSET 1900000は「190万件を一度読んでから捨てる」という意味だからです。

-- Before: 深いページ
SELECT id, product_id, rating, created_at
FROM reviews ORDER BY id OFFSET 1900000 LIMIT 20;

Before: 318.0ミリ秒。主キーにインデックスはあるのに、190万件分を読み飛ばすコストがそのままかかっていました。

-- After: 直前のページの最後のidを覚えておき、そこから続きを取る(keyset方式)
SELECT id, product_id, rating, created_at
FROM reviews WHERE id > 1900000 ORDER BY id LIMIT 20;

After: 0.330ミリ秒(約964倍)。インデックスは何も変えていません。クエリの書き方を変えただけです。keyset方式(シーク方式とも呼ばれます)は「前のページの最後の値より後ろ」を条件にするため、何ページ目であっても読む量が一定になります。無限スクロールのような「前のページに戻らない」UIとは特に相性がよい方式です。

シナリオ8: 「正確な件数」を毎回計算しない

一覧画面によくある「全123,456件」という総件数表示。これも地味に重いクエリで、しかもテーブルが育つほど線形に遅くなっていきます。

SELECT count(*) FROM access_logs;

Before: 569.2ミリ秒。2000万行すべてを数え上げるので、データが倍になれば単純計算で時間も倍になります。

総件数の表示に「1件単位の正確さ」が本当に必要かを考え直します。多くの場合、統計情報(PostgreSQLがANALYZEのたびに更新している概算の行数)で十分です。

SELECT reltuples::bigint AS estimated_count
FROM pg_class WHERE relname = 'access_logs';

After: 0.049ミリ秒(約11,600倍)。実測でも2000万という正確な値が返ってきました(直前にANALYZEしていたため)。もちろんこの値は更新頻度によって多少ズレますが、「だいたい2000万件」の表示にズレは実害がないケースがほとんどです。正確な件数がどうしても必要な場面(請求件数の確定など)だけ、通常のCOUNT(*)を使うという使い分けが現実的です。

シナリオ9: 相関サブクエリとLATERAL JOIN

「各顧客の直近の注文」を求めるような、行ごとに関連データを1件だけ取りたい場面で、相関サブクエリ(外側の行に依存するサブクエリ)を列の数だけ書いてしまうケースです。

SELECT c.id, c.name,
  (SELECT o.id FROM orders o WHERE o.customer_id = c.id
     ORDER BY o.ordered_at DESC LIMIT 1) AS latest_order_id,
  (SELECT o.total_amount FROM orders o WHERE o.customer_id = c.id
     ORDER BY o.ordered_at DESC LIMIT 1) AS latest_amount
FROM customers c;

Before: 31,906.2ミリ秒(約32秒)。今回の検証の中でもっとも遅い結果です。顧客30万人×サブクエリ2本=60万回もの実行が発生し、しかもそれぞれが「該当注文を全部読んでから並び替えて先頭1件を選ぶ」という重い処理でした。

対処はLATERAL JOIN(外側の行を参照できるJOIN)で2本のサブクエリを1本にまとめ、さらに複合インデックスで「その顧客の最新注文」を索引だけで一撃で引けるようにします。

CREATE INDEX idx_orders_customer_orderedat ON orders (customer_id, ordered_at DESC);

SELECT c.id, c.name, lo.id AS latest_order_id, lo.total_amount AS latest_amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.id, o.total_amount FROM orders o
  WHERE o.customer_id = c.id ORDER BY o.ordered_at DESC LIMIT 1
) lo ON true;

After: 1521.1ミリ秒(約21倍)。サブクエリを2本→1本にしたことと、複合インデックスで「絞り込み+並び替え」を一度に済ませられるようにしたこと、両方が効いています。「行ごとに1件だけ関連データが欲しい」というパターンでは、まずLATERALを検討する価値があります。

プランナを味方につける

シナリオ4: 不要なJOINと、繰り返す集計はマテリアライズドビューへ

ORMの「とりあえず関連を全部JOINしておく」設定のまま育った、都道府県×カテゴリ別の完了注文売上レポートを再現しました。SELECT句にもGROUP BY句にも使っていないpayments・shipmentsまで1対1でJOINしています。

SELECT c.prefecture, pc.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN product_categories pc ON pc.id = p.category_id
JOIN payments pay ON pay.order_id = o.id
JOIN shipments s ON s.order_id = o.id
WHERE o.status = 'completed'
GROUP BY c.prefecture, pc.name;

Before: 7276.3ミリ秒。実行計画を見ると、work_mem(1回の並び替えやハッシュ処理に使えるメモリ量。デフォルトに近い4MBで検証)が足りず、ハッシュ処理がディスクに溢れていました(Batches: 64, temp written=204856など)。

まず使っていないJOINを外し、work_memを一時的に引き上げてディスクへの溢れを止めます。

SET work_mem = '64MB';
-- payments/shipmentsのJOINを削除したクエリを再実行

ここで5224.1ミリ秒まで縮みましたが、まだ遅いままです。今回のstatus = 'completed'は全体の90%を占める条件で、そもそもインデックスを足しても意味がありません(ほぼ全件読むのでSeq Scanが正解のプランです)。この遅さの正体は「インデックス不足」ではなく、「1500万行の注文明細を、リクエストのたびにゼロから集計し直している」こと自体です。

こういうレポート系のクエリの本当の対処は、集計結果をマテリアライズドビュー(あらかじめ計算しておいた結果を実体のテーブルとして持つ仕組み)に逃がすことです。

CREATE MATERIALIZED VIEW mv_prefecture_category_sales AS
SELECT c.prefecture, pc.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN product_categories pc ON pc.id = p.category_id
WHERE o.status = 'completed'
GROUP BY c.prefecture, pc.name;

-- 更新は日次バッチなどで
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_prefecture_category_sales;

SELECT * FROM mv_prefecture_category_sales;

After: 0.124ミリ秒(約58,000倍)。1つの遅いクエリに「使っていないJOIN」「work_mem不足」「そもそも都度集計している」という複数の原因が重なっていたことになります。実務のチューニングも、こうやって1つずつ剥がしていくと根っこに辿り着けることが多いです。

シナリオ10: 拡張統計でプランナの見積もりミスを直す

PostgreSQLは「この条件に何件くらい該当しそうか」を統計情報から見積もり、その見積もりをもとにJOINの方式(Nested LoopかHash Joinか)や実行順序を決めます。ところがこの見積もりは、デフォルトでは列同士が独立している(関係がない)前提で計算されるため、実際には強く相関している列の組み合わせで大きく外れることがあります。

今回のデータでは、注文の配送先都道府県は顧客の居住都道府県と8割一致するように作っています(実務あるあるの「自宅に届けることが多い」という傾向の再現です)。この相関を、CREATE STATISTICSが単一テーブル内の列にしか使えない制約に合わせて、注文テーブルに顧客の都道府県をスナップショットとして複製する設計(実務でもよくある非正規化)で再現しました。

SELECT o.id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_prefecture = '東京都' AND o.shipping_prefecture = '東京都';

Before: 2403.0ミリ秒。プランナの見積もりは1,162行でしたが、実際には30,151行がヒットしていました(約26倍の過小評価)。この見積もりミスのせいで、行数が少ない前提のNested Loopが選ばれ、実際には多い行数に対して非効率な実行になっていました。

CREATE STATISTICS stx_orders_pref_mcv (mcv)
  ON customer_prefecture, shipping_prefecture FROM orders;
ANALYZE orders;

CREATE STATISTICS ... (mcv)は「列の組み合わせごとの頻出パターン(Most Common Values)」を統計として記録する拡張統計です。After: 829.0ミリ秒(約2.9倍)。見積もりは38,667行まで改善し(実際の30,151行にかなり近づいた)、実行計画もNested LoopからHash Joinに切り替わりました。インデックスを1本も追加せず、統計情報を直しただけでプランナの判断そのものが変わる——これが拡張統計の効果です。「複数の条件を組み合わせたクエリだけ妙に遅い」と感じたら、まずは行数の見積もり(estimated rows)と実際の行数(actual rows)のズレを疑ってみてください。

特殊なインデックスと物理設計

シナリオ11: JSONBの中身をインデックスで検索する

商品の色・サイズ・素材といった可変の属性をJSONB型(バイナリ形式のJSON)で持たせているケースです。

SELECT id, name, price, attributes
FROM products
WHERE attributes @> '{"color": "赤", "size": "M"}';

Before: 7.9ミリ秒(商品は5万件と規模が小さいため、Seq Scanでも絶対値は小さめです)。

CREATE INDEX idx_products_attributes_gin
  ON products USING gin (attributes jsonb_path_ops);

After: 2.6ミリ秒(約3倍)。実行計画もSeq ScanからBitmap Heap Scanに変わりました。商品マスタのような数万〜数十万件規模のテーブルでJSONBの属性検索を行うなら、jsonb_path_opsを使ったGINインデックスが定番の対処です。EAV(Entity-Attribute-Value)方式で属性ごとに別テーブルへ正規化するより、可変属性が多い場合はJSONB+GINの方が設計・実装ともにシンプルに収まることが多いです。

シナリオ12: 偏った分布には「部分インデックス」

注文ステータスのうちcancelled(キャンセル済み)は全体の約1%しかありません。こういう「全体の中では少数派」のデータを検索する管理画面向けのクエリです。

SELECT id, customer_id, ordered_at, total_amount
FROM orders WHERE status = 'cancelled' ORDER BY ordered_at DESC LIMIT 100;

Before: 175.6ミリ秒(シナリオ5で作った複合インデックスがある状態でも、絞り込み後にSortが発生していました)。

全件を対象にした複合インデックスとは別に、「statusがcancelledの行だけ」を対象にした部分インデックス(条件付きインデックス)を作ります。

CREATE INDEX idx_orders_cancelled_orderedat
  ON orders (ordered_at DESC) WHERE status = 'cancelled';

After: 0.668ミリ秒(約263倍)。ここで面白いのはインデックスのサイズです。全件対象の複合インデックス(シナリオ5で作成)が86MBだったのに対し、この部分インデックスはわずか360KB(約240分の1)でした。少数派の値をよく検索するなら、部分インデックスは「速いのに軽い」一石二鳥の選択肢です。更新のたびにインデックスへ書き込むコストも、対象行が少ない分だけ小さくなります。

シナリオ14: パーティショニングで検索と削除の両方を速くする

2000万行のアクセスログのような時系列データは、月ごとのRANGEパーティション(範囲でテーブルを物理的に分割する仕組み)にするのが定石です。まずは「直近1ヶ月分の検索」で比較しました。

SELECT count(*) FROM access_logs
WHERE occurred_at >= '2026-06-01' AND occurred_at < '2026-07-01';

Before(単一の巨大テーブル): 370.6ミリ秒(2000万行全体をSeq Scan)。月次パーティションに移行して同じクエリを実行すると、After: 46.3ミリ秒(約8倍)になりました。実行計画にはParallel Seq Scan on access_logs_y2026m06とだけ出ており、該当する1ヶ月分のパーティションだけが読まれています(Partition Pruningと呼ばれる仕組みです)。

パーティショニングの本当の価値は、実は検索よりも「古いデータの削除」で発揮されます。ログの保持期間ポリシーで古いデータを消す運用を想定しました。

-- Before: 単一テーブルから1ヶ月分(約48万行)を削除
DELETE FROM access_logs WHERE occurred_at < '2024-08-01';

Before: 4131.1ミリ秒。48万行をSeq Scanで見つけて1行ずつ削除フラグを立てていく処理で、しかも削除した行は後でVACUUMによる掃除が別途必要になります。

-- After: パーティションごと切り離して破棄
ALTER TABLE access_logs_by_month DETACH PARTITION access_logs_y2024m07;
DROP TABLE access_logs_y2024m07;

After: 46ミリ秒(約90倍)。同じ48万行相当のデータが一瞬で消えました。しかもこの方法では行単位の削除処理が発生しないため、後片付けのVACUUMも不要です。「時間が経つほど増え、かつ古いものから定期的に消す」性質のテーブル(ログ・履歴・イベント)は、最初からパーティション設計にしておく価値が大きいと実感しました。

シナリオ15: work_mem不足によるディスクスピル

顧客ごとのアクセス数ランキングを求める、よくある集計クエリです。

SELECT customer_id, count(*) AS cnt
FROM access_logs WHERE customer_id IS NOT NULL
GROUP BY customer_id ORDER BY cnt DESC LIMIT 20;

Before(work_mem=4MB、多くの環境のデフォルトに近い値): 4919.0ミリ秒。実行計画には見逃せない一文がありました。

Planned Partitions: 4  Batches: 5  Memory Usage: 8249kB  Disk Usage: 84896kB
Sort Method: external merge  Disk: 7640kB

「Disk」という文字が出てきたら要注意です。集計や並び替えに必要なメモリ(work_mem)が足りず、一時ファイルとしてディスクに溢れて(スピルして)いることを意味します。ディスクI/Oはメモリよりずっと遅いため、これが遅さの直接の原因でした。

SET work_mem = '256MB';

After: 2092.8ミリ秒(約2.35倍)。実行計画からはBatches: 1 Memory Usage: 24601kBとなり、ディスクへの溢れが消えました。work_memはサーバー全体の設定を安易に上げると同時接続数×work_memでメモリを使い切ってしまう危険があるため、「重い集計・レポートのクエリだけ、そのセッション限定でSET work_memを上げる」という使い方が安全です。

同時アクセスが増えると起きること

シナリオ13: ホットローの奪い合いとSKIP LOCKED

ここまでは1本のクエリの実行計画の話でしたが、実務のパフォーマンス問題はもう1つ別の顔を持っています。「同時に何人がアクセスしても大丈夫か」という問題です。セール中の人気商品1点に、大量の注文が同時に殺到して在庫を1個ずつ減らすケースを、DBのベンチマークツールpgbenchで再現しました。

-- 在庫を単一行で持つ(人気商品1点)
UPDATE inventories SET stock = stock - 1 WHERE product_id = 1 AND stock > 0;

同時接続数を8→32→64と上げても、TPS(1秒あたりに処理できた件数)はまったく伸びず、むしろ頭打ちになっています。一方でレイテンシ(1件あたりの待ち時間)は同時接続数にほぼ比例して悪化しました。これは「たった1行」に全員の更新が集中し、PostgreSQLの行ロックによって処理が実質的に一列に並んで順番待ちしている状態です。インデックスの話ではなく、設計そのものに同時実行性のボトルネックがある典型例です。

対処として、在庫を1行ではなく16行の「バケット」に分割し、FOR UPDATE SKIP LOCKED(すでに誰かがロック中の行を飛ばして、空いている行を掴む機能)でロックの取り合いを分散させました。

WITH bucket AS (
  SELECT id FROM inventories
  WHERE product_id = 1 AND stock > 0
  ORDER BY random() LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE inventories SET stock = stock - 1 WHERE id = (SELECT id FROM bucket);

今度は同時接続数を上げるほどTPSが伸び続けています。同時接続数64の時点で、TPSはBeforeの約2.2倍、レイテンシは半分以下になりました。単一行への集中がボトルネックになっている疑いがあるときは、EXPLAINではなくpgbenchのような同時実行ベンチマークで確認する必要があります。

15本のBefore/Afterまとめ

同一のDockerコンテナ(shared_buffers=128MB、work_mem=4MBという、本番の巨大インスタンスではなくごく普通の設定)で実測した結果の一覧です。

改めて見ると、数百〜数万倍速くなったシナリオもあれば、2〜3倍にとどまったシナリオもあります。これは検証データの作り方(合成データゆえの文字列パターンの少なさなど)による影響もありますが、同じ「インデックスを足す」という対処でも、効き方は条件次第で大きく変わるということでもあります。

まとめ

15個のシナリオを振り返ると、対処法は大きく4つに分類できます。

• ・インデックスを足す: 外部キー・複合条件・部分一致(pg_trgm)・JSONB(GIN)・偏った分布(部分インデックス)
• ・クエリの書き方を変える: N+1のバッチ化、OFFSETをkeysetに、相関サブクエリをLATERALに、正確な件数を概算に
• ・プランナに正しい情報を与える: 不要なJOINを削る、拡張統計で見積もりを直す、繰り返す集計はマテリアライズドビューへ
• ・物理設計・実行時設定を見直す: パーティショニング、work_memの調整、同時実行を前提にしたロック設計

共通して言えるのは、EXPLAIN (ANALYZE, BUFFERS)で「見積もり(estimated rows)と実際の行数(actual rows)がどれだけ離れているか」「Seq ScanかIndex ScanかBitmap Heap Scanか」「Sortやハッシュ処理がディスクに溢れていないか」の3点を確認する癖をつけると、次にどこを直せばいいかの当たりがかなりつくということです。原因が分かれば、対処自体はインデックス1本、クエリの書き換え1行で済むことも多いです。

この記事で使った検証環境とすべてのシナリオのSQLはGitHubリポジトリにまとめています。自分のPCで同じデータを作り、DBeaverで実行計画を眺めながら、ぜひ体で確かめてみてください。