作成: 2009.01.30 更新: 2021.03.05

この記事はアピリッツの技術ブログ「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で指定できるようです．．．苦労はなんだったんだろう。