この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。
Postgresqlに限らず、インデックスを利用する/利用しないは、
DBを使ったシステムの速度を決める重要な要件だったりします。
今回は、業務中に突き当たった複数のカラムを指定していた場合
DESCやASCを使っていた場合、indexを利用しなかったpostgresの
対応方法について説明します。
なお、突き当たったバージョンは8.1.9でした。
対象はたいした事は無いsql文なのですが、こういったsql文です。
SELECT
entries.id,entries.title, entries.body, entries.entry_type, entries.created_at, entries.updated_at
FROM
entries
ORDER BY
entry_type,updated_at DESC
indexは、 entry_type, updated_atの複合indexを張っています。
ですがコレがindexを利用しない。
DESC句を外すと利用するんですけどね。
というわけで幾つか検索した所、以下の2つの情報を参照して解決しました。
http://itpro.nikkeibp.co.jp/article/COLUMN/20051117/224817/?P=5
http://d.hatena.ne.jp/poch-7003/20070913/1189691482
今回はtimestamp型ですので、postgresから
rails_db=# ¥df *cmp
とした結果の
Schema | Name | Result data type | Argument data types
————+———————-+——————+———————————————————-
pg_catalog | bitcmp | integer | bit, bit
pg_catalog | bpcharcmp | integer | character, character
pg_catalog | btabstimecmp | integer | abstime, abstime
pg_catalog | btarraycmp | integer | anyarray, anyarray
pg_catalog | btboolcmp | integer | boolean, boolean
pg_catalog | btbpchar_pattern_cmp | integer | character, character
pg_catalog | btcharcmp | integer | “char”, “char”
pg_catalog | btfloat48cmp | integer | real, double precision
pg_catalog | btfloat4cmp | integer | real, real
pg_catalog | btfloat84cmp | integer | double precision, real
pg_catalog | btfloat8cmp | integer | double precision, double precision
pg_catalog | btint24cmp | integer | smallint, integer
pg_catalog | btint28cmp | integer | smallint, bigint
pg_catalog | btint2cmp | integer | smallint, smallint
pg_catalog | btint42cmp | integer | integer, smallint
pg_catalog | btint48cmp | integer | integer, bigint
pg_catalog | btint4cmp | integer | integer, integer
pg_catalog | btint82cmp | integer | bigint, smallint
pg_catalog | btint84cmp | integer | bigint, integer
pg_catalog | btint8cmp | integer | bigint, bigint
pg_catalog | btname_pattern_cmp | integer | name, name
pg_catalog | btnamecmp | integer | name, name
pg_catalog | btoidcmp | integer | oid, oid
pg_catalog | btoidvectorcmp | integer | oidvector, oidvector
pg_catalog | btreltimecmp | integer | reltime, reltime
pg_catalog | bttext_pattern_cmp | integer | text, text
pg_catalog | bttextcmp | integer | text, text
pg_catalog | bttintervalcmp | integer | tinterval, tinterval
pg_catalog | byteacmp | integer | bytea, bytea
pg_catalog | cash_cmp | integer | money, money
pg_catalog | date_cmp | integer | date, date
pg_catalog | interval_cmp | integer | interval, interval
pg_catalog | macaddr_cmp | integer | macaddr, macaddr
pg_catalog | network_cmp | integer | inet, inet
pg_catalog | numeric_cmp | integer | numeric, numeric
pg_catalog | time_cmp | integer | time without time zone, time without time zone
pg_catalog | timestamp_cmp | integer | timestamp without time zone, timestamp without time zone
pg_catalog | timestamptz_cmp | integer | timestamp with time zone, timestamp with time zone
pg_catalog | timetz_cmp | integer | time with time zone, time with time zone
pg_catalog | varbitcmp | integer | bit varying, bit varying
(40 rows)
Argument data typesに、型が書いてあるので、使いたい型を選びます。
今回は、updated_atなので、timestamp型となり、timestamp_cmpを利用しました。
で、その逆の関数を作成します。
CREATE OR REPLACE FUNCTION timestamp_cmp_rev(TIMESTAMP, TIMESTAMP) RETURNS INTEGER AS $$ SELECT timestamp_cmp($2,$1);$$ LANGUAGE sql
その後、参考URLに書いてある通り作成しました。
CREATE OPERATOR CLASS timestamp_desc_ops FOR TYPE TIMESTAMP USING BTREE AS OPERATOR 1 >, OPERATOR 2 >=, OPERATOR 3 =, OPERATOR 4 <=, OPERATOR 5 <,FUNCTION 1 timestamp_cmp(timestamp, timestamp)
で、indexを作成しました。
CREATE INDEX index_entries_on_entry_type_and_updated_at ON
entries(entry_type, updated_at timestamp_desc_ops)
これで検索するとインデックスを使用してくれました。
もし「もっと良いやり方あるよ」と言う方いらっしゃいましたら是非教えて下さい。
なお、8.3からdescで指定できるようです．．．苦労はなんだったんだろう。
