Re: How to use index in case insensitive substing search - Mailing list pgsql-general

From Hakan Kocaman
Subject Re: How to use index in case insensitive substing search
Date
Msg-id 84AAD313D71B1D4F9EE20E739CC3B6EDE97688@ATLANTIK-CL.intern.digame.de
Whole thread Raw
In response to How to use index in case insensitive substing search  ("Andrus" <eetasoft@online.ee>)
List pgsql-general
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
>

pgsql-general by date:

Previous
From: "Andrus"
Date:
Subject: Re: How to use index in case insensitive substing search
Next
From: dananrg@yahoo.com
Date:
Subject: Re: Best open source tool for database design / ERDs?