Thread: How to make LIKE to use index in "abc%" query?

Hello.

I run

explain analyze
SELECT id FROM "table" WHERE name LIKE 'dt%';

having a btree index on "name" column. But unfortunately it uses seqscan instead of index scan, it's too slow.

I had read some mailing archives about that problem, but have not found a solution. How to fix this LIKE behaviour withour re-creation af the whole database? Is it possible?

I use windows-1251 locale, not C locale.

Le lundi 08 octobre 2007, Dmitry Koterov a écrit :
> explain analyze
> SELECT id FROM "table" WHERE name LIKE 'dt%';
>
> having a btree index on "name" column. But unfortunately it uses seqscan
> instead of index scan, it's too slow.

It seems to me you'd benefit from reading this page of the fine manual:
  http://www.postgresql.org/docs/current/interactive/indexes-opclass.html

Regards,
--
dim

On Mon, 8 Oct 2007, Dmitry Koterov wrote:

> Hello.
>
> I run
>
> explain analyze
> SELECT id FROM "table" WHERE name LIKE 'dt%';
>
> having a btree index on "name" column. But unfortunately it uses seqscan
> instead of index scan, it's too slow.
>
> I had read some mailing archives about that problem, but have not found a
> solution. How to fix this LIKE behaviour withour re-creation af the whole
> database? Is it possible?
>
> I use windows-1251 locale, not C locale.

search archives for text_pattern_ops.
In brief:

select generate_series::text as txt into tst from generate_series(1,100000);
create index tstidx on tst ( lower(txt) text_pattern_ops );
vacuum analyze tst ;

explain analyze  select * from tst where lower(txt) like lower('1000%');
                                                     QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------
  Bitmap Heap Scan on tst  (cost=13.39..483.13 rows=500 width=5) (actual
time=0.095..0.158 rows=12 loops=1)
    Filter: (lower(txt) ~~ '1000%'::text)
    ->  Bitmap Index Scan on tstidx  (cost=0.00..13.27 rows=500 width=0)
(actual time=0.066..0.066 rows=12 loops=1)
          Index Cond: ((lower(txt) ~>=~ '1000'::text) AND (lower(txt) ~<~
'1001'::text))
  Total runtime: 0.283 ms


     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83