怪怪的 postgresql varchar index

一直覺得 search DB 速度很慢。今天終於找到兇手了。  用

varchar_pattern_ops  這個 CREATE INDEX 的選項是因為 SELECT LIKE 'PATTERN%' 的情形才會用 index scan

但是沒想到竟然還需要沒有  varchar_pattern_ops 的 index，否則 SELECT str='String' 時就會用 sequential scan，這好像不太合理

seadb=# \d skeyword;
                           Table "public.skeyword"
  Column   |  Type   |                       Modifiers                       
-----------+---------+--------------------------------------------------------
 str       | text    | not null
 seq       | integer | not null default nextval('skeyword_seq_seq'::regclass)
 ascii_str | boolean |
Indexes:
    "skeyword1" btree (str varchar_pattern_ops)
    "skeyword2" btree (ascii_str)

seadb=# explain select str from skeyword where str='EDTA';
                        QUERY PLAN                        
-----------------------------------------------------------
 Seq Scan on skeyword  (cost=0.00..5460.55 rows=1 width=8)
   Filter: (str = 'EDTA'::text)
(2 rows)

seadb=# drop index skeyword1;
DROP INDEX
seadb=# create index  skeyword1 on skeyword(str varchar_pattern_ops);
CREATE INDEX
seadb=# explain select str from skeyword where str='EDTA';
                        QUERY PLAN                        
-----------------------------------------------------------
 Seq Scan on skeyword  (cost=0.00..5459.99 rows=1 width=8)
   Filter: (str = 'EDTA'::text)
(2 rows)

seadb=# create index  skeyword3 on skeyword(str);
CREATE INDEX
seadb=# explain select str from skeyword where str='EDTA';
                                QUERY PLAN                               
--------------------------------------------------------------------------
 Index Scan using skeyword3 on skeyword  (cost=0.00..8.30 rows=1 width=8)
   Index Cond: (str = 'EDTA'::text)
(2 rows)

seadb=# \d skeyword;
                           Table "public.skeyword"
  Column   |  Type   |                       Modifiers                       
-----------+---------+--------------------------------------------------------
 str       | text    | not null
 seq       | integer | not null default nextval('skeyword_seq_seq'::regclass)
 ascii_str | boolean |
Indexes:
    "skeyword1" btree (str varchar_pattern_ops)
    "skeyword2" btree (ascii_str)
    "skeyword3" btree (str)

剛好在study-area看到有人分享這個。

請問一下, like %keyword% 是說 裡面有 keyword這個字就會找出來是嗎？

用 varchar index的話,他是會建立像fulltext index一樣嗎 ? 有沒有人知道原理的。

% 就是我們平常在 filename 用的 *

應該是不會，只是用來 index search 用的。 %keyword%應該是必須用 sequential scan，index search 幫不上忙。