Thread: 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.

# 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

>    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



> -----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 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);
>
> Andrus.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

> how about:
> create index nimib2 on firma1.klient(lower(nimi) varchar_pattern_ops);
> ^^^

Hakan, thank you.
Excellent.
It works.

Andrus.