ソフト開発塾 PostgreSQL

ＩＮＤＥＸ関係

ＩＮＤＥＸを使用しているかどうかを調べるには、
EXPLAIN ANALYZE
を使用する。

一般的な、CREATE INDEXコマンドで、ＩＮＤＥＸを作成した場合、
<, <=, =, >=, >の演算子を使用して比較する場合に、B-treeインデックスの使用を検討します。
また、パターンマッチ演算子LIKE、ILIKE、~および~*を含む問い合わせでも、そのパターンが先頭文字を指定している場合B-treeインデックスを使用することができます。
しかし、サーバがCロケールを使用していない場合、特別な演算子クラスでインデックスを作成しなければなりません。
と、なっているため
CREATE INDEX name ON table (column opclass [, ...]);
次の、演算子クラスを指定する必要があるようです。

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) 前のページ