Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux