PostgreSQLで `speed * 3 > value` の比較にインデックスが効かない理由

  • PostgreSQL
  • Index
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

WHERE speed * 3 > value みたいに、カラムに算術演算をかけてから比較する条件は、speed に貼った通常のB-treeインデックスが使われない。

何が起きるか

例えばこういうテーブルとインデックスがあるとする。

CREATE INDEX idx_speed ON your_table (speed);


SELECT *
FROM your_table
WHERE speed * 3 > value;

speed にインデックスがあっても、このクエリは効かない。PostgreSQLはクエリ内の式とインデックスに登録された式がそのまま一致しないと使えないので、speed * 3 という計算結果は speed のインデックスとは別物として扱われる。speed * 3 > value を内部で speed > value / 3 に自動変形してインデックスに合わせる、みたいなことはしてくれない。

対策1: 式インデックスを作る

算術演算そのものをインデックス化する方法。

CREATE INDEX idx_speed_expression ON your_table ((speed * 3));

こうしておけば speed * 3 を使ったクエリでインデックスが使われる。ただし speed * 3 という式専用のインデックスになるので、他の演算パターン（speed * 2 とか）には効かない。

対策2: クエリを書き換える

演算をカラム側から外して、既存の speed への単純なインデックスをそのまま使う方法。

SELECT *
FROM your_table
WHERE speed > value / 3;

こっちは新しくインデックスを作らなくていいけど、演算順序や型変換には注意が要る。value が整数型で割り算すると切り捨てが起きたりするので、意図した比較になっているか確認が必要。

注意点: value が定数なのかカラムなのかで話が変わる

ここが一番大事なところで、value の正体によって上の2つの対策が効くかどうかが変わる。

value が定数やバインドパラメータ（WHERE speed * 3 > $1 のような形）なら、対策1・対策2どちらも普通にIndex Condとして使われる。

でも value が同じテーブルの別カラムだった場合は事情が違う。speed * 3 > value も speed > value / 3 も「同じ行の中の2カラム比較」になるので、どちらの対策を使ってもB-treeの通常のインデックス条件（Index Cond）にはならず、Filterとして評価されるだけになる。PostgreSQLのインデックススキャンは、スキャン前に値が決まっている条件（定数・パラメータ・外部結合由来の値など）としか組み合わせられなくて、「行ごとに変わる値」同士の比較には使えないから。

なのでカラム同士を比較したいケースでは、2カラムの差分や比を式インデックスにして、定数と比較する形に持っていく必要がある。

CREATE INDEX idx_speed_value_diff ON your_table (((speed * 3) - value));
SELECT *
FROM your_table
WHERE (speed * 3 - value) > 0;

これなら式全体がインデックスに載っていて、比較相手が定数（0）になるのでIndex Condとして使える。

speed に単純なインデックスを貼っただけでは speed * 3 > value は速くならない。式インデックスかクエリ書き換えのどちらかが要るし、value がカラムなら差分を式インデックス化するところまでやらないと効果が出ない。