Re: LIKE not using indexes (due to locale issue?) - Mailing list pgsql-general

Ow Mun Heng wrote:
> On Wed, 2008-06-25 at 14:58 +1000, Klint Gore wrote:
> > Ow Mun Heng wrote:
> > > explain select * from d_trr where revision like '^B2.%.SX'
> > > --where ast_revision  = 'B2.M.SX'
> > >
> > > Seq Scan on d_trr  (cost=0.00..2268460.98 rows=1 width=16)
> > >   Filter: ((revision)::text ~~ '^B2.%.SX'::text)
> > >
> > > show lc_collate;
> > > en_US.UTF-8
> > >
> > > Is it that this is handled by tsearch2? Or I need to do the locale to
> > > "C" for this to function?
> > >
> > See http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html.
> > It tells you how to create an index that like might use in non-C locales.
>
> Just more information. This columns is created with the varchar type.
>
> original index is created using
>
> CREATE INDEX idx_d_ast
>   ON xmms.d_trh
>   USING btree
>   (revision varchar_pattern_ops);
>
>
> CREATE INDEX idx_d_ast2
>   ON xmms.d_trh
>   USING btree
>   (revision);
>
> after creating it, seems like it is still doing the seq_scan.
> So what gives? Can I get more clues here?
>
Post your query and the explain analyze of it and how many rows are in
the table.

In my database, there's 7200 rows in items and I know that none of the
identifiers for them start with 'xb'.  As you can see below, the 1st
query is sequential and the 2nd one is using the new index.  (v8.3.0)

postgres=# show lc_collate;
       lc_collate
------------------------
 English_Australia.1252
(1 row)

postgres=# explain analyse select * from items where identifier like 'xb%';
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on items  (cost=0.00..160.18 rows=1 width=113) (actual
time=4.966..4.966 rows=0 loops=1)
   Filter: ((identifier)::text ~~ 'xb%'::text)
 Total runtime: 5.029 ms
(3 rows)

postgres=# create index anindex on items(identifier varchar_pattern_ops);
CREATE INDEX
postgres=# explain analyse select * from items where identifier like 'xb%';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Scan using anindex on items  (cost=0.00..8.27 rows=1 width=113)
(actual time=0.165..0.165 rows=0 loops=1)
   Index Cond: (((identifier)::text ~>=~ 'xb'::text) AND
((identifier)::text ~<~ 'xc'::text))
   Filter: ((identifier)::text ~~ 'xb%'::text)
 Total runtime: 0.255 ms
(4 rows)


--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au