非伝統的な PostgreSQL 最適化手法

 (hakibenita.com)
6 ポイント 投稿者 GN⁺ 2026-01-21 | 1件のコメント | WhatsAppで共有
  • PostgreSQL クエリ性能を高めるための、従来の枠にとらわれない最適化手法として、インデックス追加やクエリ書き換えではない 3つの創造的なアプローチを紹介
  • チェック制約に基づくフルテーブルスキャンの除去、関数ベースインデックスによる低カーディナリティ最適化、Hash インデックスによるユニーク制約の実装
  • 1. constraint_exclusion 設定を活用し、誤った条件のクエリで不要なスキャンを防止
  • 2. 関数ベースインデックスと 仮想生成列(virtual generated column) を利用して、インデックスサイズを削減しつつクエリの一貫性を確保
  • 3. Hash インデックスと 排他制約(exclusion constraint) を組み合わせ、大容量テキスト列のユニーク制約を効率的に実装し、保存領域を大幅に節約

チェック制約に基づくフルテーブルスキャンの除去

  • plan 列で 'free', 'pro' の値のみを許可する CHECK 制約があるにもかかわらず、'Pro' という誤ったクエリを実行すると PostgreSQL はテーブル全体をスキャンする
    • 実行計画では 10 万行をすべて読み込み、実際の結果は 0 行
  • constraint_exclusion パラメータを 'on' に設定すると、PostgreSQL が制約条件を考慮して スキャンを完全に省略する
    • 実行時間は 7.4ms から 0.008ms に短縮
  • デフォルト値は 'partition' であり、単純なクエリではむしろ プランニングのオーバーヘッドが大きくなる可能性がある
    • ただし、BI・レポーティング環境ではユーザーが誤った条件を頻繁に入力するため、'on' 設定が有用

関数ベースインデックスによる低カーディナリティ最適化

  • 1,000 万件の販売データを持つ sale テーブルで 日次売上集計クエリを実行すると、フルスキャンで 627ms かかる
  • sold_at 列に B-Tree インデックスを追加すると 187ms に短縮されるが、インデックスサイズは 214MB と大きい
  • date_trunc('day', sold_at) 式に 関数ベースインデックスを作成すると、サイズは 66MB に減り、実行時間も 145ms とさらに高速になる
    • 低カーディナリティであるため インデックス deduplication が可能
  • ただし、クエリ式がインデックス定義と正確に一致している必要があるため、式の一貫性維持が必要
    • そのために同一式を含む VIEW を作成する、または
    • PostgreSQL 18 からサポートされる 仮想生成列(virtual generated column) を追加して一貫性を自動化できる
  • 仮想生成列を使えばインデックスが自動的に活用され、小さなインデックス・高速なクエリ・式の一貫性をすべて確保できる
  • ただし、PostgreSQL 18 では 仮想列へのインデックス作成はまだ未サポートで、将来の 19 バージョンで対応予定

Hash インデックスによるユニーク制約の実装

  • 長い URL を保存する urls テーブルで 重複 URL 防止のために B-Tree ベースのユニークインデックスを作成すると、インデックスサイズは 154MB に達する
  • Hash インデックスは実際の値を保存せずハッシュ値だけを保存するため、はるかに小さい
    • PostgreSQL は標準では ユニーク Hash インデックスをサポートしていないが、
    • 排他制約(exclusion constraint) を用いて EXCLUDE USING HASH (url WITH =) の形で ユニーク制約を回避的に実装できる
  • この方法でも重複挿入時にはエラーが発生し、クエリ性能も B-Tree より高速 (0.022ms vs 0.046ms)
  • インデックスサイズは 32MB で、B-Tree と比べて 5 倍以上小さい
  • 欠点:
    • 外部キー参照不可 (REFERENCES 制約は不可)
    • INSERT ... ON CONFLICT 構文との互換性に制限がある
    • ON CONFLICT ON CONSTRAINT または MERGE 構文で代替可能
  • Hash インデックスは 大容量テキスト列のユニーク保証に適しており、外部キーが不要な場合には 空間効率の高い代替手段として有用

関連記事

1件のコメント

 
GN⁺ 2026-01-21
Hacker Newsの意見

  • インデックスが214MBで、テーブル全体の半分ほどのサイズになっている
    アナリストの立場では良いが、書き込み性能の観点ではwrite amplificationの問題が生じる
    インデックス設計は読み取り/書き込み比率によって変わるため、データウェアハウスやリードレプリカを置く理由にもなる
    非常に多くのユーザーを相手にするなら、OLTP DBにBI/OLAP向けインデックスを置かない方がよい

    • PostgreSQLがclustered index（OracleのIndex Organized Table）をサポートしてくれるとよいと思う
      テーブルのアクセスパターンが一定なら、テーブル自体をインデックスにしてwrite amplificationなしで効率を得られる

  • 最初の例では、Planをenum型として定義する方がよいと思う
    テキストより軽く、誤ったフィルタ入力時に空結果ではなくエラーになるため安全性が高い

  • 素晴らしい記事だった。PostgreSQLとMySQLを何十年も使ってきたが、この記事を読んでもなお可能性の一部しか分かっていなかったと感じた

    • 自分も10年以上Postgresを使っているが、ドキュメントを見るたびにまだ表面をなぞっているだけだと感じる。本当に強力なシステムだ
    • PostgreSQLはまるでEmacsのようだ。見た目は単純だが、実際にはOSレベルの柔軟性を持っている

  • 記事の最後で触れられていたMERGE構文が最も興味深かった
    普段はINSERT ... ON CONFLICT DO UPDATEでupsertを処理しているが、MERGEはより強力で、さまざまな状況で使えそうだ

    • MERGEはかなり前からSQL標準にあったが、PostgresはMVCCモデルにおける非原子的な問題のため導入を遅らせていた
      pganalyzeのブログ記事でも説明されている
      個人的にはINSERT ... ON CONFLICTの方を好み、どうしても必要なときだけMERGEを使い、エラー処理は慎重に行う
    • 並行性の観点ではINSERT ... ON CONFLICTの方が予測しやすい
      modern-sql.comの比較記事を参照
    • 大量のバッチ挿入なら、COPY INTOをbinary形式で使うのが最速だ。サーバー側のオーバーヘッドがほとんどない

  • 記事で扱われていなかったBRINインデックスが興味深かった
    データが単調増加するなら、非常に小さく高速なインデックスとして理想的だ

    • 必ずしも完全に単調なデータである必要はない。大部分が単調なら十分うまく機能する
      たとえばサーバーで受信するtimestampデータのように、少し順序が前後する場合でも有効だ
      UUIDv7ではpages_per_rangeを調整する必要があるかもしれない

  • hashインデックスで一意制約を付けられない点がいつも残念だ
    単にexclusion constraintへ変換するglueコードさえあれば解決しそうなのに、なぜまだ存在しないのか気になる

  • ハッシュベースの一意性検証は衝突処理ができないため、インデックスではサポートされていない
    提案された解決策も同じ問題を抱えている

    • それはまったく事実ではない。インデックスにはハッシュだけが保存されるが、テーブルには完全な値が保存されている
      Postgresはハッシュと実際の値の両方が一致したときにのみ重複と見なす
    • dbfiddleの例でも確認できる

  • 記事の内容は新鮮だった。仮想カラムとhashインデックスは興味深いが、まだエコシステムに完全には統合されていない印象がある

    • 仮想カラムはほぼ完成段階だ。PostgreSQL 18で大部分が実装された
      hashインデックスは長い間制約が多かったが徐々に改善されており、自動一意制約が残る課題だ

  • stored generated columnを使えば、そのままインデックスを作れるのではないかと思う

    • 記事では、この方法を避けたい理由を説明している
      PostgreSQL 14からサポートされているが、結果が物理的に保存されて追加ストレージを消費するためだ
    • 式ベースのpartial indexを作ることもできるのではないかと思う
    • 結局は保存領域が増えるため、記事の例では避けたいアプローチだ

  • クラウドへ移行してからは、固定サーバー環境でpgsqlを直接扱うことが減った
    記事に出てきたSQL構文ハイライトが組み込み機能なのか、それとも別ツールなのか気になる

    • 私はpgcliを使っている。トランザクション状態表示、自動補完、ハイライトなど便利な機能が多い
      ただし長いクエリをコピーすると、改行の後に自動で空白が付くのが不便だ
    • IntelliJのようなIDEを使えば、構文強調や自動補完を一緒に使える