Re: index with LIKE - Mailing list pgsql-general

hello john,

thanks for your email!

changing the index type to "text_pattern_ops" solved the problem.

I didn't quite get the point, when Joseph Shraibman first sent the link
regarding operator classes. My apologies.

However, I would not fancy to change all (hundrets) of indexes now.
Would changing the lc_collate setting to 'C' solve this issue as well?

Thanks again,


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.de       Tel. +49 1805 9977 501*
mail: steffen@topconcepts.de    Fax. +49 1805 9977 502*
--------------------------------------------------------
SMS Versand ab 9.9 Cent: http://sms-gw.topconcepts.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------
*) EUR 0,12/Min. (CNS24)



> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von
> John Sidney-Woollett
> Gesendet: Donnerstag, 17. Juni 2004 11:36
> An: Henrik Steffen
> Cc: 'Scott Marlowe'; pgsql
> Betreff: Re: [GENERAL] index with LIKE
>
>
> Henrik Steffen wrote:
>
> >hello scott,
> >
> >disable enable_seqscan still does no force the backend
> >to use indexes.
> >
> >so it looks like a locale problem, right?
> >
> >I checked lc_* vars on both servers:
> >
> >type        mainserver   slave
> >lc_collate  C            de_DE.UTF-8
> >lc_ctype    de_DE@euro   de_DE.UTF-8
> >lc_messages de_DE@euro   de_DE.UTF-8
> >....
> >
> >I guest "lc_collate" is the problem, isn't it?
> >
> >
>
> If it's an encoding issue, then you may need to change the index
> operator type as suggested in one of the previous replies:
>
> Check out the link to the indexes-opclass below, and try
> recreating one
> of the indexes in the slave with a different index operator,
> and see if
> the index starts getting used. Of course it's a pain because
> the schemas
> are then slightly different... but then so is the encoding...
>
> Hope that helps. If it does please let us know. Thanks.
>
> John Sidney-Woollett
>
> Martijn van Oosterhout wrote:
>
> > The classic issue is what encoding are the databases. Anything other
> > than C and like won't use indexes.
>
> Unless you use text_pattern_ops. See
> http://www.postgresql.org/docs/7.4/static/indexes-opclass.html
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
               http://www.postgresql.org/docs/faqs/FAQ.html