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: | Raw Message | Whole Thread | 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 .... |