Re: LIKE not using indexes (due to locale issue?)

[Ow Mun Heng <Ow.Mun.Heng@xxxxxxx>]

On Wed, 2008-06-25 at 17:00 +1000, Klint Gore wrote:
> 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.


explain analyse select count(*) from d_trr_iw
--where ast_revision like '^B2.%.SX'
where ast_revision  = 'B2.P.SX'

QUERY PLAN
Aggregate  (cost=353955.35..353955.36 rows=1 width=0) (actual time=54.565..54.566 rows=1 loops=1)
  ->  Bitmap Heap Scan on d_trr_iw  (cost=3150.63..353593.31 rows=144813 width=0) (actual time=54.557..54.557 rows=0 loops=1)
        Recheck Cond: ((ast_revision)::text = 'B2.P.SX'::text)
        ->  Bitmap Index Scan on idx_d_trr_iw_ast  (cost=0.00..3114.42 rows=144813 width=0) (actual time=54.520..54.520 rows=0 loops=1)
              Index Cond: ((ast_revision)::text = 'B2.P.SX'::text)
Total runtime: 54.662 ms


> 
> 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)
> 
There's approx 29million rows in there and using the LIKE condition will
churn it for a good 20-30min I suppose. (didn't try - live database)

> 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)
> 
> 

Could it be that it's not able to determine the B2.%.SX in there?

explain select count(*) from d_trr_iw where ast_revision like 'B2.P.SX'

even this will result in a seq_scan.