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

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