How to use index in case insensitive substing search
От | Andrus |
---|---|
Тема | How to use index in case insensitive substing search |
Дата | |
Msg-id | e63g42$rtp$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: How to use index in case insensitive substing search
|
Список | pgsql-general |
How to force postgres to use index for the following query (I can change the query to equivalent if required) select nimi from klient where lower(nimi) like 'test%' Currently it does NOT use index: create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops); explain analyze select nimi from firma1.klient where lower(nimi) like 'mokter%' "Seq Scan on klient (cost=0.00..9.79 rows=1 width=74) (actual time=0.740..0.761 rows=1 loops=1)" " Filter: (lower((nimi)::text) ~~ 'mokter%'::text)" "Total runtime: 0.877 ms" Postgres 8.1 UTF8 encoding in Windows XP Note. Query explain analyze select nimi from firma1.klient where nimi like 'Mokter%' for same data uses index: "Index Scan using nimib on klient (cost=0.00..5.80 rows=1 width=74) (actual time=9.402..9.427 rows=1 loops=1)" " Index Cond: ((nimi ~>=~ 'Mokter'::bpchar) AND (nimi ~<~ 'Moktes'::bpchar))" " Filter: (nimi ~~ 'Mokter%'::text)" "Total runtime: 9.615 ms" Andrus.
В списке pgsql-general по дате отправления: