ハクソク

世界を動かす技術を、日本語で。

非従来型のPostgreSQL最適化

110日前原文(hakibenita.com)

概要

  • PostgreSQLの 非定型的な最適化手法 を紹介
  • CHECK制約 を活用した全表走査の回避
  • 関数ベースインデックス によるストレージとパフォーマンス最適化
  • 運用現場での注意点 や制限事項も解説
  • 実践的なSQL例とともに 論理的な流れ で整理

PostgreSQLにおける型破りな最適化テクニック

  • 一般的な最適化策（クエリ修正、インデックス追加、非正規化、ANALYZE、VACUUM、CLUSTER）以外の 創造的な手法 の重要性
  • 本記事では PostgreSQL特有の非定型的な最適化方法 を掘り下げ解説

CHECK制約を活用した全表走査の回避

  • ユーザー管理テーブルにおいて planカラム に'free'と'pro'のみ許容する CHECK制約 を設定
  • 分析担当者が誤って存在しない'Pro'（大文字）で検索し、 全表走査（Seq Scan） が発生
  • PostgreSQLはデフォルトでは CHECK制約をプランニングに活用しない ため、無駄なスキャンが生じる
  • constraint_exclusion パラメータを'on'に設定することで、制約違反条件のクエリで 即時スキャン回避 が可能
  • constraint_exclusionはデフォルトで'partition'だが、 BI・レポーティング環境では'on'推奨
    • 手作業クエリでのヒューマンエラー対策

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

  • 売上テーブル（sale）で 日時（sold_at） に対しB-Treeインデックスを付与すると 巨大なインデックス（例：214MB） が生成
  • 日単位の集計が主目的の場合、 date_trunc関数＋AT TIME ZONE を使った関数ベースインデックスを作成
    • 例：CREATE INDEX sale_sold_at_date_ix ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date);
  • 関数ベースインデックスは サイズが大幅に小さく（例：66MB）、検索も高速化
  • カーディナリティが低い値 （日単位）をインデックス化することで、 ストレージ節約とパフォーマンス向上 を両立
  • ただし、 クエリ側でインデックス式と完全に一致する表現 が必要
    • わずかな違い（例：::date vs date_trunc）でも インデックスが利用されない 問題
    • 組織内での クエリ記述の統一・運用上の規律 が課題

PostgreSQL最適化の現場的注意点

  • CHECK制約とconstraint_exclusion の組み合わせは、特に アドホックなクエリが多い環境で有効
  • 関数ベースインデックス はクエリ記述の統一が必要で、現実的には 仮想生成カラム や ビュー との併用が望ましい
  • ストレージ・パフォーマンス・運用の バランス を考慮した設計が重要

まとめ

  • 従来型の最適化だけでなく、PostgreSQL独自の工夫 によりさらなる効率化が可能
  • CHECK制約＋constraint_exclusion、 関数ベースインデックス など、 現場の課題に直結する解決策 を活用
  • 運用上の制限や注意点も理解しつつ、 柔軟な最適化戦略 の構築が重要

Hackerたちの意見

すごく新鮮な内容だね。バーチャルカラムとハッシュインデックスの仕組みは面白いけど、なんか無理やり付け足した感じがするな。全体のエコシステムの一部として自然に機能するってわけじゃないみたい。

ストアド生成カラムを使うと、直接インデックスを作れるんじゃないかな？こっちの方がいいアプローチじゃない？

└

ストレージの使用量が増えると思うけど、その例ではそれを避けようとしてるって言ってたよね。

└

記事では、なぜこのオプションを避けたいのか説明してるね。 > 「バージョン14から、PostgreSQLは生成カラムをサポートしています。これは、行を挿入するときに式で自動的に埋め込まれるカラムです。まさに必要なもののように思えるけど、注意点がある。式の結果がマテリアライズされるから、追加のストレージが必要になる。これが最初に節約しようとしていたものなんだ！」

└

ストアド生成カラムを使うと、直接インデックスを作れるんじゃないかな？こっちの方がいいアプローチじゃない？式に対してインデックス（部分インデックスかも）を作ることもできるの？

いい記事だね、PostgreSQLの面白い機能がたくさん紹介されてる。自分は何十年もPostgreSQLとMySQLを使ってきたけど、この記事を読んで、まだまだ可能性の表面をなぞっただけだって気づかされたよ。

└

もう10年以上Postgresを使ってるけど、ドキュメントに触れるたびに同じ気持ちになるんだ。ほんと、表面をちょっとなぞってるだけって感じ。めっちゃパワフルだよね。

└

PostgreSQLはEmacsみたいなもんだね。何か別のものに変装したオペレーティングシステムみたい。

現在、制約除外はデフォルトで、継承ツリーを使ってテーブルのパーティショニングを実装するためによく使われるケースにのみ有効になってる。すべてのテーブルでこれをオンにすると、シンプルなクエリに対してかなり目立つ追加の計画オーバーヘッドが発生するし、ほとんどの場合、シンプルなクエリには何のメリットもない。PGのプランキャッシングの欠如がまたもや影響してるね。他のDBではクエリプランを再利用するから、こういうことは心配しなくていいんだけど。

└

PGはプランを再利用するけど、クエリを準備してその接続で5回以上実行しないとダメなんだ。plan_cache_mode[0]と、そこにリンクされてるPREPAREのドキュメントを見てみて。これは、いつも実行されるシンプルなクエリにはすごく効果的なんだけど、パラメータの値が見えなくなっちゃうから、クエリによってはうまくいかないこともある。例えば、(customer_id, item_id)にインデックスがあって、customer_id = $1 AND item_id = ANY($2)（$2は配列パラメータ）というクエリを実行すると、一般的なクエリプランは配列の要素数がわからなくて、ネストされたループ結合の代わりにビットマップインデックススキャンみたいな複雑なプランを選んじゃうことがある。こんな状況で一般的なプランがひっくり返って、負荷が100倍以上違うのを見たこともあるよ。プランキャッシュは接続ごとだから、クエリを何度もプランニングしなきゃいけないのも理由の一つだね。PGで接続を統合することが重要な理由の一つだよ。

この記事から知ってたポイントがいくつかあったよ。

この文章で一番興味深かったのは、最後にほとんどさらっと触れられたMERGEのことだった。

「SQLで制約名を使うのはあまり好きじゃないから、両方の制限を克服するためにMERGEを使うよ。」

db=# MERGE INTO urls t USING (VALUES (1000004, 'https://hakibenita.com')) AS s(id, url)
ON t.url = s.url
WHEN MATCHED THEN UPDATE SET id = s.id
WHEN NOT MATCHED THEN INSERT (id, url) VALUES (s.id, s.url);
MERGE 1

「アップサートを扱うためにinsert ... on conflict do update ...をいつも使ってるけど、MERGEの方がもっとパワフルで、いろんなシナリオで使えるかもしれないね。前に聞いたことなかった。」

└

確か、MERGEは結構前からSQLの一部だったけど、Postgresはその構文がPostgresのMVCCモデル内で本質的に非原子的だから、長い間追加しなかったんだ。 https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert... これは個人的な好みだけど、私はINSERT ... ON CONFLICTを使って、できるだけそれに合わせてデータモデルを設計するかな。もしMERGEの一般的な機能が絶対に必要で、INSERT ... ON CONFLICTで代替案を設計できないなら、MERGEのエッジケース（失敗）をうまく処理するためにちょっと時間をかけるつもりだよ。

└

大量のバッチ挿入をするなら、COPY INTOを使うのが一番早いって感じてる。特にバイナリデータ形式を使うと、Postgresサーバー側のオーバーヘッドがないからね。

数年前にクラウドに移ったから、最近はpgsqlみたいな固定サーバーインフラで遊ぶ機会が減っちゃったな。pgsqlには今、構文ハイライトが組み込まれてるの？それとも別のラッパーが提供してるのかな？（すごく見栄えがいいね）。

└

IntelliJみたいなIDEを使えば、構文ハイライトやコード補完ができるよ。

└

その目的で私はpgcliを使ってるよ。うまく動くし、トランザクションの状態がわかりやすかったり、再接続が良かったり、構文ハイライトや補完が普通のpsqlよりも多くのケースでうまく機能するのがいいね（外部キーの関係が定義されてるときには句の補完もできる！）。唯一の不満は、クエリが長すぎると改行の後にスペースを追加するのが面倒で、長いクエリをコピー＆ペーストするのが大変になることかな。

ハッシュインデックス方式は、ユニークな「ハッシュ」カラムを作ってアプリケーションやクエリでハッシュを事前計算するのに比べて、絶対的に優れてるのかな？

一意性のためのハッシュ技術は、ハッシュ衝突を処理できないからインデックスではサポートされてないんだ。著者が提案した解決策も同じ問題を抱えてて、テーブルにまだ存在しない値が、すでに保存されているものと同じハッシュを持ってると、たまに拒否されちゃうんだよね。