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

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)

hubert depesz lubaczewski <depesz@depesz.com> writes:
> 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:

= is not one of the members of the text_pattern_ops operator class.

regression=# select amopopr::regoperator from pg_amop where amopclaid in (select oid from pg_opclass where opcname =
'text_pattern_ops');
     amopopr
-----------------
 ~<~(text,text)
 ~<=~(text,text)
 ~=~(text,text)
 ~>=~(text,text)
 ~>~(text,text)
 ~=~(text,text)
(6 rows)

            regards, tom lane

On Sat, Sep 15, 2007 at 11:09:39AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > 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:
> = is not one of the members of the text_pattern_ops operator class.

ok, but is there any reason for this? i mean - i'm not really sure why
this index cannot be used.

best regards

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)

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Sat, Sep 15, 2007 at 11:09:39AM -0400, Tom Lane wrote:
>> = is not one of the members of the text_pattern_ops operator class.

> ok, but is there any reason for this?

Well, at the time those opclasses were invented, the regular = operator
didn't necessarily yield the same result --- in some locales strcoll()
can return "equal" for not-bitwise-equal strings.

As of a couple years ago, the regular text = operator only yields true
for bitwise-equal strings, so we could perhaps drop ~=~ and use = in its
place.  But I'd be worried about breaking existing queries that expect
the strangely-named operator to be there.

The operator class structure only permits one equality operator per
opclass, so supporting both is not feasible.

            regards, tom lane

On Sat, Sep 15, 2007 at 11:48:19AM -0400, Tom Lane wrote:
> As of a couple years ago, the regular text = operator only yields true
> for bitwise-equal strings, so we could perhaps drop ~=~ and use = in its
> place.  But I'd be worried about breaking existing queries that expect
> the strangely-named operator to be there.

ok. fair enough. it's clearer now.

best regards,

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)