一般的な、CREATE INDEXコマンドで、ＩＮＤＥＸを作成した場合、 <, <=, =,
>=, >の演算子を使用して比較する場合に、B-treeインデックスの使用を検討します。
また、パターンマッチ演算子LIKE、ILIKE、~および~*を含む問い合わせでも、そのパターンが先頭文字を指定している場合B-treeインデックスを使用することができます。
しかし、サーバがCロケールを使用していない場合、特別な演算子クラスでインデックスを作成しなければなりません。
と、なっているため
CREATE INDEX name ON table (columnopclass [, ...]);
次の、演算子クラスを指定する必要があるようです。 text_pattern_ops、varchar_pattern_ops、bpchar_pattern_opsおよびname_pattern_ops演算子クラスは、それぞれ、text、varchar、charおよびname型上のB-treeインデックス
となる。
よって、
=とLIKEとを利用する場合、ＩＮＤＥＸは、それぞれ必要になるようです。
テスト実行結果
テスト用のコマンド群
EXPLAIN ANALYZE
SELECT * FROM klsh_fs WHERE fs_url = 'http://www.kondo.net.gr.jp/';
EXPLAIN ANALYZE
SELECT * FROM klsh_fs WHERE fs_url like 'http://www.kondo.net.gr.jp/';
EXPLAIN ANALYZE
SELECT * FROM klsh_fs WHERE fs_url like 'http://www.kondo.net.gr.jp/%';
DROP INDEX index_klsh_fs_url_ops;
EXPLAIN ANALYZE
SELECT * FROM klsh_fs WHERE fs_url like 'http://www.kondo.net.gr.jp/';
EXPLAIN ANALYZE
SELECT * FROM klsh_fs WHERE fs_url like 'http://www.kondo.net.gr.jp/%';
EXPLAIN ANALYZE
SELECT * FROM klsh_fs WHERE fs_url >= 'http://www.kondo.net.gr.jp/' and fs_url < 'http://www.kondo.net.gr.jp0';
CREATE INDEX index_klsh_fs_url_ops ON klsh_fs (fs_url varchar_pattern_ops);
テスト用の結果出力
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using index_klsh_fs_url on klsh_fs (cost=0.00..6.01 rows=2 width=736) (actual time=0.229..0.229 rows=0 loops=1)
Index Cond: ((fs_url)::text = 'http://www.kondo.net.gr.jp/'::text)
Total runtime: 0.324 ms
(3 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_klsh_fs_url_ops on klsh_fs (cost=0.00..6.01 rows=2 width=736) (actual time=0.154..0.154 rows=0 loops=1)
Index Cond: ((fs_url)::text ~=~ 'http://www.kondo.net.gr.jp/'::character varying)
Filter: ((fs_url)::text ~~ 'http://www.kondo.net.gr.jp/'::text)
Total runtime: 0.214 ms
(4 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_klsh_fs_url_ops on klsh_fs (cost=0.00..6.01 rows=1 width=736) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: (((fs_url)::text ~>=~ 'http://www.kondo.net.gr.jp/'::character varying) AND ((fs_url)::text ~<~ 'http://www.kondo.net.gr.jp0'::character varying))
Filter: ((fs_url)::text ~~ 'http://www.kondo.net.gr.jp/%'::text)
Total runtime: 0.063 ms
(4 rows)
DROP INDEX
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on klsh_fs (cost=0.00..35332.59 rows=2 width=736) (actual time=7135.125..7135.125 rows=0 loops=1)
Filter: ((fs_url)::text ~~ 'http://www.kondo.net.gr.jp/'::text)
Total runtime: 7135.253 ms
(3 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on klsh_fs (cost=0.00..35332.59 rows=1 width=736) (actual time=7168.743..7168.743 rows=0 loops=1)
Filter: ((fs_url)::text ~~ 'http://www.kondo.net.gr.jp/%'::text)
Total runtime: 7168.811 ms
(3 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_klsh_fs_url on klsh_fs (cost=0.00..6.01 rows=1 width=736) (actual time=0.142..0.142 rows=0 loops=1)
Index Cond: (((fs_url)::text >= 'http://www.kondo.net.gr.jp/'::text) AND ((fs_url)::text < 'http://www.kondo.net.gr.jp0'::text))
Total runtime: 0.212 ms
(3 rows)
CREATE INDEX
このテスト結果より、通常のＩＮＤＥＸ作成では、WHERE fs_url like 'http://www.kondo.net.gr.jp/';と、記述すると
INDEXは、使用せずに検索している。ことがわかります。
また、上記のようにならないためには、initdbを行うときに --no-locateを指定する
ソフト開発塾ソフト開発塾(PostgreSQL)前のページへ戻る