Re: How to use index in case insensitive substing search

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(dot)kocaman(at)digame(dot)de

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andrus
> Sent: Tuesday, June 06, 2006 10:58 AM
> To: pgsql-general(at)postgresql(dot)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
>