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@xxxxxxxxx > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Andrus > Sent: Tuesday, June 06, 2006 10:58 AM > To: pgsql-general@xxxxxxxxxxxxxx > 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 >
