Hello if you have non-c locale, you have to use varchar_pattern_ops - like: create index like_index on lidi (prijmeni varchar_pattern_ops); Regards Pavel Stehule On 18/02/2008, T.J. Adami <adamitj@xxxxxxxxx> wrote: > On 4 fev, 18:13, "Andrus" <kobrule...@xxxxxx> wrote: > > I need to perform case insensitive search by first some characters (ABC) of > > name like > > > > SELECT ... > > FROM customer > > WHERE upper(customername) like 'ABC%' > > > > My database cluster locale is non-C > > Database encoding is UTF-8 > > > > Which index I must create so that this query can use it to speed up data > > retrieval ? > > > > Andrus. > > CREATE INDEX ie_cust_upper_name ON customer ( UPPER( customername ) ); > > If you want to perform better performance for "like 'ABC%'" statement, > you can define it on WHERE clause: > > CREATE INDEX ie_cust_upper_name ON customer ( UPPER( customername ) ) > WHERE ( UPPER(customername) LIKE 'ABC%' ); > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
