Re: index with LIKE - Mailing list pgsql-general

Henrik

Thanks for the info.

What encoding you should use depends on your data, and how you want
records sorted etc. You'll have to figure out what is more suitable for
you - I cannot answer that for you.

To solve your master/slave index problem, why not rebuild the slave
databases using the C encoding instead of your de_DE.UTF-8 encoding?

Otherwise create a SQL statement to extract all the (text) indexes from
your database where a like operation will be used, and use it to drop
and re-create the index.

Something along these lines can be used to drop your indexes ( you need
to execute the results from the query)

SELECT 'drop index '||n.nspname||'.'||c.relname||';'
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
AND n.nspname IN ('customer', 'photo')
AND c.relname NOT LIKE '%_pkey';

But you'll need to be more specific about which schemas and indexes to
drop and re-create. The SQL to regenerate the new indexes, I'll leave to
you to figure out! :)

Good luck.

John Sidney-Woollett

Henrik Steffen wrote:

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