PostgreSQL

はじめに バッチ処理で大量のデータ変換を行う際、PostgreSQLのCTE（Common Table Expression、WITH句）を多用していた時期がありました。複雑な変換処理を段階的に分割できて、コードの見通しも良くなる便利な機能です。 しかし、実際の現場でCTEを使っているコードは意外と少ない。サブクエリや一時テーブルが使われているケースの方が圧倒的に多い印象です。 この記事では、実務でCTEを使って感じた強み・弱みと、「なぜ現場ではCTEが少ないのか」を考察します。特にPostgreSQL 12で大きく改善された最適化の仕組みについても解説します。 CTEの基本おさらい CTEはWITH句を使って一時的な結果セットを定義し、メインクエリから参照できる機能です。 WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ) SELECT region, total_sales FROM regional_sales WHERE total_sales > 10000; サブクエリと似ていますが、名前をつけて再利用できる点が特徴です。変数のように扱えて、複雑なクエリを段階的に構築できます。 CTEの強みと弱み 強み 1. 可読性・保守性の向上 ネストしたサブクエリ地獄を回避できます。処理を論理的なステップに分割して、各ステップに名前をつけられるため、コードレビューやメンテナンスが格段に楽になります。 2. 再帰クエリが書ける WITH RECURSIVEを使えば、階層構造（組織図、カテゴリツリー）を扱えます。これはCTE独自の強みで、サブクエリでは実現できません。 3. 複数箇所から参照できる 同じCTEを複数回参照できます（ただし最適化の観点で注意が必要、後述）。サブクエリだと同じ処理を重複して書く必要があります。 4. デバッグしやすい 各CTEを個別に実行して中間結果を確認できます。サブクエリだと抜き出して実行するのが面倒です。 5. 変換処理の分離 SELECT句での複雑な計算を先にCTEで処理しておけます。WHERE句で使いたいけど計算が複雑な場合に便利です。 弱み 1. 親クエリのパラメータを参照できない サブクエリなら外側の列を参照できる（相関サブクエリ）のに対し、CTEは独立しているため参照できません。 -- サブクエリなら可能 SELECT * FROM orders o WHERE amount > (SELECT AVG(amount) FROM orders WHERE region = o.region); -- CTEでは不可能（外側のo.regionを参照できない） 2. 大量データ・長時間処理には不向き メモリ上に保持されるため、巨大データだと辛い。一時テーブルならインデックスを作成したり統計情報を活用できます。 実際、バッチ処理で数百万行のデータを扱う際、CTEよりも一時テーブルの方がパフォーマンスが良いケースが多かったです。 3. PostgreSQL 11以前は「最適化バリア」になる これが最大の問題でした。次のセクションで詳しく解説します。 ...

参考 この記事は、以下の記事を読んで疑問に思ったことを調べた学習記録である。 Zennの検索スピードを5倍に高速化した話 記事では、Zennのサイト内検索をpg_trgm拡張を使って平均6倍、95パーセンタイルで4.25倍高速化した事例が紹介されている。 なぜ中間一致検索は遅いのか 通常、PostgreSQLでLIKE '%keyword%'のような中間一致検索を実行すると、BTreeインデックスが使えずフルスキャンが発生する。BTreeインデックスは文字列の前方一致には有効だが、中間一致では活用できない構造になっているためである。 データ量が増えると、このフルスキャンが深刻なパフォーマンスボトルネックになる。参考記事では、検索に1秒〜数秒かかる状態だったとのことだ。 n-gramインデックスの仕組み n-gramインデックスは、文字列をn文字ずつに分割してインデックス化することで、中間一致検索でもインデックスを効かせる仕組みである。 3-gramの例 「PostgreSQL」という文字列を3-gram（トライグラム）で分割すると以下のようになる。 __P, _Po, Pos, ost, stg, tgr, gre, reS, eSQL, QL_, L__ 先頭と末尾にはパディング文字（_）が付与される。 検索時の動作 「stgre」というキーワードで検索する場合： 検索キーワードを3-gramで分割: stg, tgr, gre インデックスからこれらすべてのトライグラムを含む文書を抽出 抽出された候補に対してRecheck処理を実行 重要なのは「いずれか」ではなく「すべて」のトライグラムが存在する文書が候補になる点である。もし「いずれか」だと、無関係な文書が大量に候補に含まれてしまう。 Recheck処理が必要な理由 n-gramインデックスでは、インデックスレベルでの検索後に必ずRecheck処理が必要になる。 具体例 以下のような状況を考える。 本文: 「小学校校長」 クエリ: 「小学校長」 3-gramで分割すると： 「小学校校長」→ 小学校, 学校校, 校校長 「小学校長」→ 小学校, 学校長 「小学校」が共通しているため、n-gramレベルでは「小学校校長」が候補として抽出される。しかし実際には「小学校長」という文字列は含まれていない。 このようなfalse positive（誤検出）を除外するため、インデックスで絞り込んだ候補に対して、実際に検索キーワードが含まれているかを厳密にチェックする必要がある。これがRecheck処理である。 pg_trgmとpg_bigmの選択 PostgreSQLには2つの主要なn-gram拡張がある。 pg_trgm: 3-gram方式、PostgreSQL本体にcontribとして付属 pg_bigm: 2-gram方式、サードパーティ製（NECが開発） 比較表 機能 pg_trgm pg_bigm エコシステム PostgreSQLコミュニティ サードパーティ ILIKE対応 ○ × 2文字以下の検索 × ○ Recheck無効化 × ○ インデックスサイズ 小 大（約2倍） なぜpg_trgmが選ばれたか 参考記事では、以下の理由でpg_trgmのみを採用している。 ...