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
>