cht電腦資訊SQL DB
adm Find login register

postgresql 的 SELECT LIKE bug

eliu

joined: 2007-08-09
posted: 11480
promoted: 617
bookmarked: 187
新竹, 台灣
1subject: postgresql 的 SELECT LIKE bugPromote 0 Bookmark 02007-12-19quote  

理論上 SELECT LIKE 'xxxx%' 應該可以用 INDEX search,結果沒有,不知道為什麼?

難道與 PRIMARY KEY 有關?還是 bug ? 

 

db =# explain SELECT seq FROM tab WHERE str like 'edt%';
                        QUERY PLAN                        
-----------------------------------------------------------
 Seq Scan on tab  (cost=0.00..6465.55 rows=1 width=4)
   Filter: (str ~~ 'edt%'::text)
(2 rows)

db=# explain SELECT seq FROM tab WHERE str = 'edta';
                                  QUERY PLAN                                 
------------------------------------------------------------------------------
 Index Scan using tab_pkey on tab  (cost=0.00..8.30 rows=1 width=4)
   Index Cond: (str = 'edta'::text)
(2 rows)

eliu

joined: 2007-08-09
posted: 11480
promoted: 617
bookmarked: 187
新竹, 台灣
2subject: Promote 0 Bookmark 02007-12-19quote  

發了 bug report 給 postgresql

eliu

joined: 2007-08-09
posted: 11480
promoted: 617
bookmarked: 187
新竹, 台灣
3subject: Promote 0 Bookmark 02007-12-21quote  

It's not bug. a LIKE 'some%' is different operation than a = str_const. if you don't use C locale, then you have to use varchar_pattern_ops. please try:

create index like_index on tab(str varchar_pattern_ops);

don't forget do ANALYZE tab before Regards Pavel Stehule

原來是要這樣。

edited: 4

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