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

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: text_pattern_ops index *not* used in field = value condition?
Date: 2007-09-15 14:48:26
Message-ID: 20070915144826.GA17914@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-09-15 15:09:39 Re: text_pattern_ops index *not* used in field = value condition?
Previous Message Tom Lane 2007-09-15 14:47:12 Re: strange TIME behaviour