How to use index in case insensitive substing search
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.
do you have an index on klient (lower(nimi))?
Yes. As I wrote in first message, I created index explicity for this test
sample:
create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops);
Andrus.
Hi Andrus,
how about:
create index nimib2 on firma1.klient(lower(nimi) varchar_pattern_ops);
^^^
explain analyze select nimi from firma1.klient where lower(nimi) like
'mokter%'"Total runtime: 0.877 ms"
explain analyze select nimi from firma1.klient where nimi
like 'Mokter%'for same data uses index:
"Total runtime: 9.615 ms"
Hmm...Index-use seems to slow down the query.
Best regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrus
Sent: Tuesday, June 06, 2006 10:58 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to use index in case insensitive
substing searchHow 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);
Andrus.
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Import Notes
Resolved by subject fallback
# eetasoft@online.ee / 2006-06-06 11:58:26 +0300:
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%'
do you have an index on klient (lower(nimi))?
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991