cht電腦資訊SQL DB
adm Find login register

怪怪的 postgresql varchar index

eliu

joined: 2007-08-09
posted: 11468
promoted: 617
bookmarked: 187
新竹, 台灣
1subject: 怪怪的 postgresql varchar indexPromote 0 Bookmark 02008-06-10quote  

一直覺得 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)

edited: 2
企鵝狂
joined: 2008-04-01
posted: 185
promoted: 20
bookmarked: 7
2subject: Promote 0 Bookmark 02010-04-30quote  

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

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

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

 

eliu

joined: 2007-08-09
posted: 11468
promoted: 617
bookmarked: 187
新竹, 台灣
3subject: Promote 0 Bookmark 02010-05-01quote  

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

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

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

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

企鵝狂
joined: 2008-04-01
posted: 185
promoted: 20
bookmarked: 7
4subject: Promote 0 Bookmark 02010-05-01quote  
可是如果都要用sequencial scan的話,幹嘛還要建立index...?
eliu

joined: 2007-08-09
posted: 11468
promoted: 617
bookmarked: 187
新竹, 台灣
5subject: Promote 0 Bookmark 02010-05-01quote  
企鵝狂
可是如果都要用sequencial scan的話,幹嘛還要建立index...?
所以要建對的 index,沒辦法用的 index 就變成必須用 sequential scan。
edited: 1

cht電腦資訊SQL DB
adm Find login register
views:15867