データベースクエリの最適化: インデックス、実行プラン、パーティショニング

インデックスが 1 つ欠けていると、2 ミリ秒のクエリが 20 秒のテーブル スキャンに変わる可能性があります。 データベースが数千行から数百万行に増加するにつれて、最適化されたクエリと最適化されていないクエリの違いは、応答性の高いアプリケーションと負荷がかかるとタイムアウトになるアプリケーションの違いとなります。データベースの最適化は、実行可能なパフォーマンス作業の中で最も高いエンジニアリング時間の利益をもたらします。

重要なポイント

• EXPLAIN ANALYZE は最も強力な診断ツールです -- 何かを最適化する前に実行計画を読む方法を学びましょう
• インデックス タイプを戦略的に選択します: 等価性と範囲には B ツリー、フルテキストと JSONB には GIN、フィルタリングされたサブセットには部分インデックス
• N+1 クエリは、ORM ベースのアプリケーションで最も一般的なパフォーマンスの低下要因です -- クエリ ログを使用して早期に検出します
• テーブルの行数が 1,000 ～ 5,000 万行を超えるとテーブルのパーティショニングが必須となり、クエリの計画時間を短縮し、効率的なデータ ライフサイクル管理を可能にします。

---

EXPLAIN ANALYZE による実行計画の読み取り

クエリを最適化する前に、PostgreSQL が現在クエリをどのように実行しているかを理解する必要があります。 EXPLAIN ANALYZE はクエリを実行し、実際のタイミング データを含む実際の実行プランを表示します。

基本的な EXPLAIN ANALYZE 出力には、プランナーが選択した戦略、推定行数と実際の行数、および各ステップで費やされた時間が表示されます。注目すべき主要な指標は次のとおりです。

• Seq Scan -- データベースはテーブル内のすべての行を読み取ります。小さなテーブル (10,000 行未満) には許容されますが、大きなテーブルの場合は危険信号です。
• インデックス スキャン -- データベースはインデックスを使用して、一致する行を効率的に検索します。これは、大きなテーブルに対するフィルターされたクエリに必要なものです。
• インデックスのみスキャン -- データベースは、テーブルには触れずにインデックスから完全にクエリに応答します。最も高速なスキャンタイプ。
• ネストされたループ -- 外部テーブルの行ごとに内部テーブルを 1 回スキャンしてテーブルを結合します。内部スキャンでインデックスを使用する場合に効率的です。
• ハッシュ結合 -- 結合の一方の側からハッシュ テーブルを構築し、もう一方の側でそれをプローブします。より大きな結果セットの場合に効率的です。
• 並べ替え -- 明示的な並べ替えステップ。多くの場合、ORDER BY に使用されます。ディスクに溢れるソートに注意してください (「ソート方法: 外部マージ」で示されます)。

何を探すべきか

実行計画における最も重要なシグナルは、推定された行と実際の行の間のギャップです。 PostgreSQL が 10 行と見積もったのに 100,000 行が見つかった場合、間違ったプランが選択されたことになります。これはテーブル統計が古い場合に発生します。テーブルに対して ANALYZE を実行して統計を更新します。

大きなテーブルでの順次スキャン、インデックスを使用しないソート、内部テーブルでの順次スキャンによるネストされたループに注意してください。これらの各パターンは、インデックスが欠落しているか、再書き込みが必要なクエリを示しています。

---

インデックスの種類とそれらを使用する場合

PostgreSQL にはいくつかのインデックス タイプが用意されており、それぞれがさまざまなクエリ パターンに合わせて最適化されています。適切なタイプを選択することが重要です。等価性チェックのみが必要な列の GIN インデックスは、ストレージを浪費し、読み取りは改善されずに書き込みが遅くなります。

B ツリー インデックス

B ツリーは、デフォルトで最も汎用性の高いインデックス タイプです。等価 (=)、範囲 (<、>、BETWEEN)、並べ替え (ORDER BY)、およびプレフィックス パターン マッチング (LIKE 'abc%') をサポートします。 WHERE、JOIN、ORDER BY 句のほとんどの列では、B ツリー インデックスが正しい選択です。

複合インデックスは、複数の列を単一の B ツリーに結合します。列の順序は重要です。(status, created_at) のインデックスは、status のみ、または status と created_at の両方でフィルタリングするクエリを効率的にサポートしますが、created_at だけではサポートしません。最も選択性の高い列を最初に配置し、範囲フィルターに使用する列を最後に配置します。

GIN インデックス

GIN インデックスは、複合値内の検索に優れています。これらは、全文検索 (tsvector 列)、JSONB 包含クエリ (@>、?)、および配列重複クエリ (&&、@>) に不可欠です。 GIN インデックスは B ツリー インデックスよりも大きく、更新が遅いため、B ツリーがクエリ パターンを処理できない場合にのみ使用してください。

柔軟な属性を格納する JSONB 列の場合、列全体の GIN インデックスはキーベースのクエリをサポートします。特定のキーのみをクエリする列の場合、生成された列または式に対する B ツリー インデックスの方が効率的です。

部分インデックス

部分インデックスは、WHERE 条件に一致する行のみをインデックスします。これらは、クエリで一貫してデータの小さなサブセットをフィルター処理するテーブルに強力です。

たとえば、orders テーブルに 1,000 万行があり、アクティブな注文 (テーブルの 5%) をほぼ独占的にクエリする場合、(customer_id, created_at) WHERE status = 'active' の部分インデックスは完全なインデックスよりも 20 倍小さく、実際のクエリと同じ速度になります。

---

N+1 クエリの検出と修正

N+1 クエリの問題は、ORM を使用するアプリケーションで最も一般的なパフォーマンスの問題です。この問題は、コードが N レコードのリストをロードし、レコードごとに 1 つの追加クエリを実行して関連データをロードし、合計クエリが 1 ～ 2 ではなく N+1 になる場合に発生します。

N+1 クエリの発生方法

顧客名を含む注文のリストをロードすることを検討してください。単純な実装では、注文リスト (1 クエリ) がロードされ、次に注文ごとに顧客 (N クエリ) がロードされます。注文が 100 件ある場合、データベースのラウンドトリップが 101 回発生します。クエリあたり 1 ミリ秒では、つまり 101 ミリ秒になります。ただし、接続プールの競合による同時負荷がかかると、簡単に 500 ミリ秒以上になる可能性があります。

検出方法

1. クエリ ログ -- PostgreSQL クエリ ログを一時的に有効にし、異なるパラメータ値で繰り返される同一のクエリを探します。
2. ORM レベルのログ -- Drizzle ORM、Prisma、および TypeORM はすべて、実行されたすべての SQL ステートメントを示すクエリ ログをサポートしています。
3. APM ツール -- Datadog、New Relic、Sentry はエンドポイントごとにクエリをグループ化し、N+1 パターンを自動的にハイライトできます
4. pg_stat_statements -- この PostgreSQL 拡張機能はクエリ実行統計を追跡し、頻繁に実行される同一のクエリ テンプレートを明らかにします。

N+1 クエリの修正

修正は ORM とクエリ パターンによって異なります。

• 積極的な読み込み -- JOIN を使用して最初のクエリで関連データをロードするように ORM に指示します。 Drizzle では、クエリ ビルダーで with オプションを使用します。
• バッチ読み込み -- すべての外部キー ID を収集し、単一の WHERE id IN (...) クエリで関連レコードを読み込みます。これは DataLoader パターンです。
• 非正規化 -- 読み取りが多い使用例の場合は、関連データを親レコードに直接保存します。読み取りパフォーマンスのために書き込みの複雑さをトレードします。

---

クエリ書き換え手法

場合によっては、インデックスの改善だけでなく、クエリ自体の再構築が必要になることがあります。

サブクエリから JOIN への変換

相関サブクエリは、外側のクエリの行ごとに 1 回実行されます。それらを JOIN に変換すると、PostgreSQL はより効率的な結合戦略を使用できるようになります。

顧客ごとの最新の注文日を検索するサブクエリを使用して注文を選択する代わりに、派生テーブルまたはウィンドウ関数を使用した JOIN としてサブクエリを書き換えます。 JOIN バージョンを使用すると、PostgreSQL はデータ分散に基づいてネストされたループ、ハッシュ結合、マージ結合のいずれかを選択できます。

共通テーブル式 (CTE)

PostgreSQL 12 以降では、CTE はデフォルトでインライン化されます。これは、オプティマイザが述語を CTE にプッシュできることを意味します。パフォーマンス フェンスを気にせずに読みやすくするために CTE を使用します。 (高価なサブクエリの再実行を防ぐため) 明示的にマテリアライゼーションが必要な場合は、MATERIALIZED キーワードを追加します。

ウィンドウ関数と GROUP BY

詳細行と集計の両方が必要な場合、ウィンドウ関数を使用すると、自己結合やサブクエリが必要なくなります。累計の計算、グループ内のランキング、各行とグループの平均との比較はすべて、相関サブクエリよりもウィンドウ関数を使用した方が効率的です。

---

テーブルのパーティショニング戦略

テーブルが 1,000 ～ 5,000 万行を超えると、適切にインデックスが作成されたクエリでも、インデックスの深さ、バキュームのオーバーヘッド、およびプランナーの複雑さにより速度が低下します。パーティショニングでは、単一の論理テーブル インターフェイスを維持しながら、大きなテーブルを小さな物理チャンクに分割します。

パーティションの種類

日付による範囲分割

最も一般的なパターンは、タイムスタンプ列による月次パーティション化です。各月のデータは独自のパーティションに保存されます。日付でフィルタリングするクエリは、関連するパーティションのみを自動的にスキャンします (パーティション プルーニング)。

時間ベースのパーティショニングの利点:

• クエリのパフォーマンス -- 最近のデータのクエリは最近のパーティションのみをスキャンします
• メンテナンス -- VACUUM と ANALYZE は、パーティションが小さいほど高速に実行されます。
• データ ライフサイクル -- 古いパーティションの削除は、数百万行の削除に比べて瞬時に行われます。
• バックアップの効率 -- ポイントインタイム リカバリのために最近のパーティションのみをバックアップします。

パーティショニングに関する考慮事項

パーティショニングにより複雑さが増します。パーティション プルーニングが機能するには、すべてのクエリの WHERE 句にパーティション キーが含まれている必要があります。一意の制約にはパーティション キーを含める必要があります。パーティション化されたテーブルを参照する外部キーには制限があります。テーブル サイズがパフォーマンス低下の原因となっていることが測定された場合にのみ、パーティショニングを開始してください。

---

PostgreSQL 構成のチューニング

デフォルトの PostgreSQL 構成は保守的で、最小限のハードウェアで実行されるように設計されています。実稼働ワークロードは、主要なパラメータを調整することで恩恵を受けます。

これらの設定は、特定のハードウェアとワークロードに合わせて調整する必要があります。 pg_stat_bgwriter、pg_stat_activity、および pg_stat_user_tables を監視して、変更によりパフォーマンスが向上することを検証します。

---

よくある質問

テーブルにはいくつのインデックスが必要ですか?

固定された制限はありませんが、インデックスを維持する必要があるため、各インデックスにより INSERT、UPDATE、および DELETE 操作が遅くなります。経験則としては、最も頻繁に使用するクエリの WHERE、JOIN ON、ORDER BY 句に出現する列のインデックスを作成することです。 pg_stat_user_indexes を使用して、削除できる未使用のインデックスを見つけます。

パフォーマンスのためには UUID または整数の主キーを使用する必要がありますか?

整数主キー (BIGSERIAL) は、サイズが小さく (8 バイト対 16 バイト)、自然に順序付けされているため、結合とインデックス作成が高速になります。 UUID は調整なしでグローバルな一意性を提供しますが、これは分散システムにとって重要です。ほとんどのアプリケーションでは、外部向けの識別子には UUID を使用し、内部結合には整数を使用します。

単一データベースから読み取りレプリカに切り替える必要があるのはいつですか?

読み取りワークロードがデータベース容量の 70 ～ 80% を超えた場合、またはレポート クエリがリソースに関してトランザクション クエリと競合した場合。リードレプリカは読み取り負荷を処理し、プライマリは書き込みに重点を置きます。これは、一般的な Web アプリケーションの同時ユーザー数が 5,000 ～ 10,000 の場合に必要になります。

本番環境でダウンタイムなしで遅いクエリを処理するにはどうすればよいですか?

テーブルのロックを回避するには、CONCURRENTLY オプションを使用してインデックスを作成します。 pg_stat_statements を使用して、最も遅いクエリを特定します。機能フラグの背後にクエリの最適化を展開します。テーブルを書き換えるスキーマ変更の場合は、pg_repack などのツールを使用して、ロックせずにテーブルを再編成します。

---

次は何ですか

データベースの最適化は、プラットフォームのパフォーマンスの基礎です。まず pg_stat_statements を有効にし、最も遅いクエリを特定し、EXPLAIN ANALYZE を使用して体系的に処理します。不足しているインデックスを追加し、N+1 パターンを修正し、最大のテーブルのパーティション分割を検討してください。

より広範なパフォーマンスの全体像については、[ビジネス プラットフォームをスタートアップからエンタープライズまで拡張する] (/blog/scaling-business-platform-performance) に関する柱ガイドを参照してください。最適化の次の層については、Redis、CDN、HTTP キャッシュを使用したキャッシュ戦略 に関するガイドをお読みください。

ECOSIRE は、Odoo ERP やカスタム アプリケーションなどの PostgreSQL ベースのプラットフォーム向けに専門的なデータベース最適化を提供します。データベースのパフォーマンス監査については、お問い合わせ してください。

---

ECOSIRE によって発行 — Odoo ERP、Shopify eCommerce、OpenClaw AI にわたる AI を活用したソリューションで企業のスケールアップを支援します。