キーワード検索でインデックスを利用するようにする about jvn HOT 1 CLOSED

jvn_db=# CREATE INDEX jvn_vulnerability_idx_2 ON jvn_vulnerability(title text_pattern_ops);
CREATE INDEX
時間: 571.341 ms
jvn_db=# analyze;
ANALYZE
時間: 9822.300 ms
jvn_db=# EXPLAIN ANALYZE select * from jvn_vulnerability where title like 'WordPress%';
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jvn_vulnerability  (cost=75.00..2839.99 rows=2055 width=444) (actual time=1.465..18.550 rows=2318 loops=1)
   Filter: (title ~~ 'WordPress%'::text)
   ->  Bitmap Index Scan on jvn_vulnerability_idx_2  (cost=0.00..74.49 rows=1017 width=0) (actual time=1.110..1.110 rows=2318 loops=1)
         Index Cond: ((title ~>=~ 'WordPress'::text) AND (title ~<~ 'WordPrest'::text))
 Total runtime: 18.804 ms
(5 行)

時間: 20.615 ms
jvn_db=#