Re: 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