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

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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83