i have this table:
# \d text_words
Table "public.text_words"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
id | integer | not null default nextval('text_words_id_seq'::regclass)
word | text | not null
Indexes:
"text_words_pkey" PRIMARY KEY, btree (id)
"ui_text_words" UNIQUE, btree (word text_pattern_ops)
index is created using text_pattern_ops so i will be able to use it in 'where word like '...%''
but, it appears it is not usable with = operator:
# explain analyze select * from text_words where word = 'a';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on text_words (cost=0.00..861.88 rows=1 width=13) (actual time=11.517..26.520 rows=1 loops=1)
Filter: (word = 'a'::text)
Total runtime: 26.567 ms
(3 rows)
# set enable_seqscan = false;
SET
# explain analyze select * from text_words where word = 'a';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on text_words (cost=100000000.00..100000861.88 rows=1 width=13) (actual time=11.299..23.595 rows=1 loops=1)
Filter: (word = 'a'::text)
Total runtime: 23.643 ms
(3 rows)
is there any particular reason for it?
of course i can change "=" to like, and then index is being used:
# explain analyze select * from text_words where word like 'a';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using ui_text_words on text_words (cost=0.00..8.28 rows=1 width=13) (actual time=0.095..0.099 rows=1
loops=1)
Index Cond: (word ~=~ 'a'::text)
Filter: (word ~~ 'a'::text)
Total runtime: 0.237 ms
(4 rows)
but it seems wrong. or am i missing something?
pg version is 8.3devel straight from cvs head.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
