| From: | "Andrus" <eetasoft(at)online(dot)ee> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | How to use index in case insensitive substing search |
| Date: | 2006-06-06 08:58:26 |
| Message-ID: | e63g42$rtp$1@news.hub.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andrus | 2006-06-06 09:50:31 | Re: How to use index in case insensitive substing search |
| Previous Message | Rafal Pietrak | 2006-06-06 08:07:46 | ALTER USER ..... PASSWORD .... |