範囲指定条件でのインデックス

PostgreSQL でのお話。単純な例に置き換える。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' AND 得点 > 80;

上の SQL 文は、テストの得点が 80 点の場合の B 組内での順位を求めるもの。

この生徒テーブルに (得点, クラス) の複合インデックスを張ってたんだけど、EXPLAIN 文で調べても上記の SQL 文でこのインデックスが使われてる気配がない。
おかしいな、PostgreSQL はバカだなあ、こんなの Oracle だったらテーブルスキャンすることもなくインデックスだけ見れば解決できそうじゃん。

でも、さっき自分が間違っていることに気付いた。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' AND 得点 = 80;

これならインデックスは使われる。が、実際の SQL は「得点 > 80」と、範囲を指定しているので、複合インデックスのキーは (クラス, 得点) という順になっていなければいけなかった。つまり、以下の SQL 文にインデックスを張るようなイメージで考えればよかった。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' ORDER BY 得点;
※この SQL は正しくないですよ。

複合インデックスの最初のキーに持ってくるのはカーディナリティが高いカラムであるべきという考えに縛られてしまってて気付かなかった。

さらに、指定する得点によってはインデックスが使われないケースも。そこで WHERE 句に条件を追加(得点は100点満点とする)。

SELECT count(*) + 1 FROM 生徒 WHERE クラス = 'B組' AND 得点 > 80 AND 得点 <= 100;

これで、EXPLAIN 文で調べていた限りではインデックスが使われるようになった。

Last updated on July 7, 2015