text_pattern_ops index *not* used in field = value condition?

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема text_pattern_ops index *not* used in field = value condition?
Дата
Msg-id 20070915144826.GA17914@depesz.com
обсуждение исходный текст
Ответы Re: text_pattern_ops index *not* used in field = value condition?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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)

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: strange TIME behaviour
Следующее
От: Tom Lane
Дата:
Сообщение: Re: text_pattern_ops index *not* used in field = value condition?