
PostgreSQL 17では、クエリ最適化機能と実行エンジンが大幅に強化され、パフォーマンスチューニングに対する洗練されたアプローチが求められます。このガイドでは、実行計画の解読、新しいメモリ管理機能の活用、複雑なクエリパターンの最適化を通じて、最高のパフォーマンスを実現する方法を詳しく解説します。
Analyze queries for sargability, missing composite indexes & offsets
Analyze SQL queries for performance anti-patterns, wildcards, and slow offsets. Generates optimized queries and DDL composite indexes for PostgreSQL, MySQL, and SQLite.
EXPLAIN ANALYZE出力の理解PostgreSQLのクエリ最適化の基礎は、EXPLAIN ANALYZE出力の解釈にあります。このコマンドは、プランナーが推定した実行計画と実際のランタイム統計を提供します。
EXPLAINツリーの各ノードは操作を表します。主なメトリクスは次のとおりです。
cost: プランナーが推定したコスト (起動..合計)。rows: プランナーが推定した行数。actual time: 実際に費やされた時間 (起動..合計、ミリ秒単位)。actual rows: 実際に返された行数。loops: ノードが実行された回数。Buffers: 共有、ローカル、一時ブロックアクセスに関する詳細 (BUFFERSオプションが必要)。Wal: WALレコード生成統計 (WALオプションが必要)。Settings: 計画に影響を与えるGUCパラメータ (SETTINGSオプションが必要)。簡単なクエリを考えてみましょう。
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.price > 100 AND c.category_name = 'Electronics';
典型的な出力は次のようになります。
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=25.80..50.35 rows=10 width=64) (actual time=0.150..0.250 rows=5 loops=1)
Hash Cond: (p.category_id = c.category_id)
Buffers: shared hit=10 read=2
Wal: records=0, full_page_writes=0, bytes=0
-> Seq Scan on products p (cost=0.00..24.50 rows=20 width=36) (actual time=0.010..0.100 rows=15 loops=1)
Filter: (price > 100::numeric)
Rows Removed by Filter: 85
Buffers: shared hit=5 read=1
-> Hash (cost=25.75..25.75 rows=1 width=32) (actual time=0.030..0.030 rows=1 loops=1)
Buffers: shared hit=5 read=1
-> Seq Scan on categories c (cost=0.00..25.75 rows=1 width=32) (actual time=0.010..0.020 rows=1 loops=1)
Filter: (category_name = 'Electronics'::text)
Rows Removed by Filter: 99
Buffers: shared hit=5 read=1
Planning Time: 0.100 ms
Execution Time: 0.300 ms
解釈:
Hash Join: トップレベルの操作。完了までに0.250 msかかり、5行を返しました。プランナーは10行と推定しました。Seq Scan on products p: productsテーブルをスキャンしました。85行をフィルタリングし、15行を保持しました。このスキャンは5個の共有ブロックにヒットし、ディスクから1を読み取りました。Hash: categoriesテーブルスキャンからハッシュテーブルを構築しました。Seq Scan on categories c: categoriesをスキャンし、99行をフィルタリングし、1行を保持しました。このスキャンも5個の共有ブロックにヒットし、ディスクから1を読み取りました。Buffersセクションは非常に重要です。shared hitは共有バッファ(メモリ)で見つかったデータを示し、readはディスクからフェッチする必要があったデータを示します。readのカウントが高い場合、インデックスの欠落やshared_buffersの不足を示していることがよくあります。
rows(推定)とactual rowsの間の不一致は危険信号です。大きな不一致は、プランナーが誤った判断を下したことを示しており、その原因は次のいずれかであることが多いです。
ANALYZEまたはVACUUM ANALYZEを実行します。WHERE句に苦慮します。ANALYZEでdefault_statistics_targetを増やすと役立ちます。CREATE STATISTICSは複数列または関数依存性の統計を提供できます。推定ミスの例:
もしactual rowsのSeq Scan on products pが10000だったのに、rowsが20だった場合、プランナーはNested LoopではなくHash JoinやMerge Joinを選択してしまい、ひどいパフォーマンスにつながる可能性があります。
work_memとmaintenance_work_memPostgreSQL 17では、メモリ管理の改善が続けられています。work_memとmaintenance_work_memの適切なチューニングが重要です。
work_memこのパラメータは、一時データをディスクに書き込む前に、クエリ操作(例:ソート、ハッシュテーブル)が使用できるメモリの最大量を定義します。各操作はこの量を使用できます。
EXPLAIN ANALYZE出力でspillやdiskとして表示されます(例:Sort Method: external merge Disk: 12345kB)。チューニング戦略:
EXPLAIN ANALYZEでSort Method: external merge DiskまたはHashAggregate (actual time=... loops=1) Memory: 12345kB, Disk: 67890kBを探します。4MBまたは8MB)から始めます。スピルが発生する場合は、増やします。一般的な本番環境での値は、セッションあたり64MBから256MBですが、これはワークロードと利用可能なRAMに大きく依存します。SET work_mem TO '256MB';を使用できます。-- Example: Observe a sort spill
-- Assume 'large_table' has millions of rows and 'some_column' is not indexed
SET work_mem TO '64kB'; -- Artificially low for demonstration
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table
ORDER BY some_column;
-- Output might show:
-- Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
-- Sort Key: some_column
-- Sort Method: external merge Disk: 12345kB <-- This indicates a spill
-- Buffers: ...
-- Increase work_mem to prevent spill
SET work_mem TO '256MB'; -- Adjust based on actual spill size and available RAM
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM large_table
ORDER BY some_column;
-- Output should ideally show:
-- Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
-- Sort Key: some_column
-- Sort Method: quicksort Memory: 12345kB <-- In-memory sort
-- Buffers: ...
maintenance_work_memVACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEYなどのメンテナンス操作に使用されます。これらの操作は通常、頻度は低いですが、非常にメモリを消費する可能性があります。
チューニング戦略:
work_memよりも高く設定します。一般的な値は、最大のテーブルのサイズと利用可能なRAMに応じて、256MBから1GB以上です。-- Example: Creating an index on a large table
-- A low maintenance_work_mem can make this very slow.
SET maintenance_work_mem TO '16MB'; -- Artificially low
CREATE INDEX idx_large_table_col ON large_table (another_column);
-- This might take a very long time and use temp files.
-- Increase for faster index creation
SET maintenance_work_mem TO '1GB'; -- Or higher, depending on table size
CREATE INDEX idx_large_table_col_new ON large_table (yet_another_column);
-- This should be significantly faster.
PostgreSQL 17では、インクリメンタルソートの利用とハッシュ結合のパフォーマンス向上に関して、プランナーの機能が強化されています。
インデックスによって既にソートされている列のサブセットでソートを必要とするクエリの場合、PostgreSQLは「インクリメンタルソート」を実行できます。これにより、完全なソート操作が回避され、コストが大幅に削減されます。
-- Assume an index on (order_date, customer_id)
CREATE INDEX idx_orders_date_customer ON orders (order_date, customer_id);
-- Query that can benefit from incremental sort
EXPLAIN (ANALYZE)
SELECT *
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY order_date, customer_id, order_total DESC;
EXPLAIN出力は次のようになるかもしれません。
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Sort Key: order_total DESC
Presorted Key: order_date, customer_id <-- Indicates incremental sort
-> Index Scan using idx_orders_date_customer on orders (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Index Cond: (order_date >= '2023-01-01'::date)
Presorted Keyの行が重要な指標です。最適化するには:
ORDER BY句の先頭列をカバーしていることを確認します。Sort Key列はインクリメンタルにソートされます。ハッシュ結合は、大規模でソートされていないデータセットを結合するのに効率的です。PostgreSQL 17では、メモリ使用量とスピル処理が改善されています。
work_memが、より小さいリレーションのハッシュテーブルをメモリに保持するのに十分であることを確認してください。ディスクにスピルすると、パフォーマンスが低下します。enable_hashjoin: このGUCがon(デフォルト)であることを確認してください。-- Example: Hash Join
EXPLAIN (ANALYZE, BUFFERS)
SELECT a.id, b.value
FROM large_table_a a
JOIN medium_table_b b ON a.b_id = b.id;
出力:
QUERY PLAN
--------------------------------------------------------------------------------------
Hash Join (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Hash Cond: (a.b_id = b.id)
Buffers: ...
-> Seq Scan on large_table_a a (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
-> Hash (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Memory: 12345kB <-- Hash table memory usage
Buffers: ...
-> Seq Scan on medium_table_b b (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
もしHashノードのMemoryの後にDiskが続く場合、ハッシュテーブルがスピルしたことを意味します。work_memを増やしてください。
PostgreSQL 17では、並列クエリ機能が引き続き強化されており、複数のCPUコアが単一クエリの一部を処理できるようになっています。
max_parallel_workers_per_gather: 単一のGatherまたはGather Mergeノードに対して起動できる並列ワーカーの最大数を制御します。max_parallel_workers: システム全体で許可される並列ワーカーの総数。min_parallel_table_scan_size: 並列スキャンが考慮される最小テーブルサイズ(KB単位)。parallel_setup_cost: 並列ワーカーを起動するための推定コスト。parallel_tuple_cost: ワーカーからリーダーにタプルを転送するための推定コスト。並列計画の特定:
EXPLAIN ANALYZE出力でGatherまたはGather Mergeノードを探します。
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM very_large_table
WHERE some_column > 100;
出力:
QUERY PLAN
--------------------------------------------------------------------------------------
Finalize Aggregate (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Buffers: ...
-> Gather (cost=... rows=... width=...) (actual time=... rows=... loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: ...
-> Partial Aggregate (cost=... rows=... width=...) (actual time=... rows=... loops=2)
Buffers: ...
-> Parallel Seq Scan on very_large_table (cost=... rows=... width=...) (actual time=... rows=... loops=2)
Filter: (some_column > 100)
Buffers: ...
最適化:
max_parallel_workers_per_gatherが適切に設定されていることを確認します(例:最新のサーバーでは4から8)。min_parallel_table_scan_sizeが高すぎないことを確認します。| 機能/パラメータ | 説明 | デフォルト (PG17) | 推奨範囲 | トレードオフ |
|---|---|---|---|---|
work_mem | クエリごとのソート/ハッシュ操作用メモリ。 | 4MB | 64MB - 256MB | 低すぎると: ディスクスピル、クエリが遅くなる。高すぎると: 同時実行クエリでOOM。 |
maintenance_work_mem | VACUUM、CREATE INDEX用メモリ。 | 64MB | 256MB - 1GB+ | 低すぎると: メンテナンスが遅くなる。高すぎると: メンテナンス中にアクティブなクエリに影響。 |
shared_buffers | メインデータキャッシュ。 | 128MB | RAMの25% - 40% | 低すぎると: 高いディスクI/O。高すぎると: OSキャッシュの冗長性、OOM。 |
effective_cache_size | プランナーのOS + shared_buffersの推定値。 | 4GB | RAMの50% - 75% | プランナーへの情報提供用。メモリは割り当てない。不正確な値は悪い計画につながる。 |
max_parallel_workers_per_gather | クエリごとの最大並列ワーカー数。 | 2 | 4 - 8 | 低すぎると: CPUを十分に活用できない。高すぎると: 過剰なコンテキストスイッチ、リソース競合。 |
default_statistics_target | 統計の粒度。 | 100 | 200 - 1000 | 低すぎると: 行推定が不正確。高すぎると: ANALYZE時間が増加、pg_statisticテーブルが大きくなる。 |
落とし穴: VACUUM FULLまたはREINDEX後の突然のパフォーマンス低下
Index Scanの代わりにSeq Scanが発生します。VACUUM FULLとREINDEXはテーブル/インデックスを書き換えるため、統計が古くなったり、プランナーのキャッシュが無効になったりする一時的な状態につながる可能性があります。ANALYZE VERBOSE;を実行します。これにより、プランナーは正確な統計を再構築するよう強制されます。REINDEXの場合、インデックス再構築後にテーブルに対してANALYZEが実行されることを確認してください。落とし穴: 高い設定にもかかわらずwork_memスピルが発生する
EXPLAIN ANALYZEが、work_memが十分に高い値(例:256MB)に設定されているにもかかわらず、Sort Method: external merge DiskまたはHashAggregate ... Diskを示す。work_memは操作ごとに割り当てられます。単一の複雑なクエリには複数のソートまたはハッシュ操作があり、それぞれがwork_memを消費する可能性があります。また、多くの同時実行クエリが実行されている場合、合計メモリ消費量がシステムRAMを超える可能性があります。work_memをさらに増やす: 単一の重要なクエリである場合は、そのセッションのwork_memを増やしてみてください。ORDER BYまたはGROUP BYのインデックスを追加します。ソート/ハッシュの前にデータセットのサイズを減らすようにクエリをリファクタリングします。pg_stat_activityを使用して、アクティブなクエリのtemp_bytesを確認します。すべてのクエリの合計temp_bytesが高い場合、システム制限に達している可能性があります。落とし穴: プランナーが最適でない結合順序を選択する
EXPLAIN ANALYZEが、大きな中間結果セットを早期に処理する結合順序を示す。WHERE句が原因であることが多い。ANALYZE: 関係するすべてのテーブルに最新の統計があることを確認します。CREATE STATISTICS: 複数列の相関関係や関数依存性については、拡張統計を作成します。
CREATE STATISTICS my_correlation_stats ON (col1, col2) FROM my_table;
ANALYZE my_table;
WHERE句を簡素化する: 可能であれば、複雑な述語を分解します。SET join_collapse_limit / SET from_collapse_limit: これらのGUCを一時的に減らして、プランナーにさらに多くの結合順序を検討させます。ただし、これにより計画時間が長くなる可能性があります。SET enable_nestloop = off;: 特定の結合タイプを一時的に無効にして、プランナーに代替案を検討させます。デバッグに役立ちます。落とし穴: EXPLAIN ANALYZEにおける高いPlanning Time
Planning Timeが常に高い(例:数百ミリ秒から数秒)。UNION操作。default_statistics_target: 精度は向上しますが、値が高いほど計画中に処理するデータが多くなります。enable_seqscan = off: あらゆる場所でインデックススキャンを強制すると、プランナーがインデックスを見つけるのに苦労する可能性があります。default_statistics_targetがワークロードに対して過度に高くないことを確認します。データベース管理者が、特定のテーブルに対して、`EXPLAIN ANALYZE` の出力で `Buffers: shared read` の数が頻繁に高く表示されることに気づきました。そのテーブルは比較的小さく、頻繁にアクセスされているにもかかわらずです。この観察の最も可能性の高い直接的な原因は何ですか、そしてさらに調査するための最も直接的な行動は何ですか?
work_memが正しく設定されているかどうかはどうすればわかりますか?
EXPLAIN ANALYZE出力でSort Method: external merge DiskまたはHashAggregate ... Diskを確認してください。これらが表示される場合、その特定の操作に対してwork_memが低すぎます。また、pg_stat_statementsでtemp_bytesとtemp_filesを監視して、全体的な一時ファイルの使用状況を確認してください。
CREATE STATISTICSはいつ使用すべきですか?
CREATE STATISTICSは、プランナーが、WHERE句、JOIN条件、またはGROUP BY句に複数の列を含むクエリに対して、常に不正確な行推定を行う場合に使用します。特に、これらの列に相関データがある場合に有効です。これにより、プランナーは単一列の統計では見落とす列間の関係を理解するのに役立ちます。
shared_buffersとeffective_cache_sizeの違いは何ですか?
shared_buffersは、PostgreSQLがデータキャッシュのために実際に割り当てるメモリです。effective_cache_sizeは、クエリプランナーに、shared_buffersやOSファイルシステムキャッシュを含む、データキャッシュに利用できると期待されるメモリ量を伝えるGUCです。メモリを割り当てるわけではありませんが、ディスクI/Oのプランナーのコスト推定に影響を与えます。
インデックスがあるのに、私のクエリはIndex ScanではなくSeq Scanを使用しています。なぜですか?
これは通常、プランナーがシーケンシャルスキャンの方がインデックススキャンよりも高速であると推定した場合に発生します。一般的な理由は次のとおりです。
ANALYZEを実行してください。Seq Scanの方が高速であることがよくあります。random_page_cost: このGUCが高すぎると、ランダムI/O(インデックススキャン)がより高価に見えるようになります。REINDEXが役立つかもしれません。PostgreSQLに特定の計画を使用させるにはどうすればよいですか?
データが変更された場合に最適でない計画につながる可能性があるため、一般的には推奨されませんが、GUCを使用して特定の計画タイプを一時的に無効にすることで、プランナーに影響を与えることができます(例:SET enable_seqscan = off;、SET enable_nestloop = off;)。よりきめ細かな制御が必要な場合は、pg_hint_plan(拡張機能)などのツールを使用するとSQLレベルのヒントが可能ですが、これは細心の注意を払い、徹底的なテストの後でのみ使用すべきです。最善のアプローチは、根本的な統計またはインデックスの問題を修正することです。
AI CLIログの整形、Cron式の作成、JWTの解析、chmod権限の計算をオフラインで実行。

高並行性下におけるPostgreSQLコネクションプーリングのPgCat、PgBouncer、Supavisorを、実運用で検証された事例を交えて詳細に解説するアーキテクチャガイドです。
続きを読む
PostgreSQLのテーブルとインデックスの肥大化を診断・解消します。自動バキュームのチューニング方法、pg_repackによるゼロダウンタイムでの再構築、MVCCの可視性マップまでを解説します。
続きを読む
PostgreSQLの変更データキャプチャ(CDC)を、Debezium、Kafka Connect、トランザクションアウトボックスを用いた本番環境レベルのアーキテクチャとコード例で網羅的に解説する包括的なガイドです。
続きを読む