PostgreSQL8.1で2カラムの日付検索にインデックスを使う
- 2016/06/24
- aikawa
RHEL5系、まだまだ現役です。2020年まで。
開始日と終了日の有るテーブルで、「今日」がこの期間内に有るデータを検索する場合、開始日と終了日で個別にインデックスを貼る事になるかと思いますが、極端に過去や未来のデータが多くなったりすると、片方のインデックスが無駄になります。
btreeで複数列にインデックスを作っても、そのままではインデックスを使ってくれなかったので、かなりねじった方向で出来ました。
CREATE INDEX test_index ON tb
using btree(
(EXTRACT(EPOCH FROM date_st)::int8),
(EXTRACT(EPOCH FROM date_ed)::int8)
);
EXPLAIN SELECT * FROM tb
WHERE EXTRACT(EPOCH FROM date 'today')::int8
BETWEEN EXTRACT(EPOCH FROM date_st)::int8 AND EXTRACT(EPOCH FROM date_ed)::int8
;
結果はこんな感じでBitmap Indexを使ってくれました。
Bitmap Heap Scan on tb (cost=311.24..15402.21 rows=10457 width=524)
Recheck Cond: ((1466694000::bigint >= (date_part('epoch'::text, (date_st)::timestamp without time zone))::bigint) AND (1466694000::bigint <= (date_part('epoch'::text, (date_ed)::timestamp without time zone))::bigint))
-> Bitmap Index Scan on test_index (cost=0.00..311.24 rows=10457 width=0)
Index Cond: ((1466694000::bigint >= (date_part('epoch'::text, (date_st)::timestamp without time zone))::bigint) AND (1466694000::bigint <= (date_part('epoch'::text, (date_ed)::timestamp without time zone))::bigint))
いや、でも実際8.4なら下みたいにINDEX にDESC入れるとBETWEENで普通にインデックス使ってくれるらしいんで、あんま役に立たないんですけど。
CREATE INDEX test_index on tb using btree(date_st, date_ed DESC);
SELECT * FROM tb WHERE date 'today' BETWEEN date_st AND date_ed;
範囲型がまだ無いんで、ちょっと面倒なのは変わらず。
もっと簡単に実現出来そうなら、こっそり教えて下さい。
