Fwd: Question on Trigram GIST indexes

(forwarded to pgsql-general after it went to Kevin Grittner alone)

On 22 December 2012 22:46, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:

> ERR ORR wrote:
>
> > Specifically, I was trying to replicate what is done in this blog post:
> >
> http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html
>
> > Queries which use "WHERE "TST_PAYLOAD" LIKE 'SEAT%'" go to the btree
> index
> > as it should.
> > Queries which use "WHERE "TST_PAYLOAD" LIKE '%EAT%'" *should* use the
> GIST
> > index but do a full table scan instead.
> > (I am looking for names like 'SEATTLE')
>
> Have you run VACUUM ANALYZE with the index and data in place (as
> shown in the blog post?
>
> Another conspicuous difference is your explicit use of a COLLATE
> clause in the index declaration.
>
> -Kevin
>

a) Yes, I ran VACUUM ANALYZE after creating the indexes.
b) The COLLATE pg_catalog."default" clause is inserted by the DB, I run the
CREATE INDEX command without that.
"Default" collation for all my DBs in Postgres is en_US.UTF-8 and both the
system (Linux FC17) and the DB
use encoding UTF8.

I have texts/strings in different languages/charsets, so UTF8 looked like
the best decision to me, instead of, say, ISO-8859-15, which is limited to
just some European charsets. Specifically I am storing strings in European
languages (corresponding to the ISO-8859 series) including diacrites line
äöüñáéíóú ..., Russian, Arabic, Chinese etc. in one column instead of
making different columns/tables and using them via a view because that's my
use case and UTF8 should accommodate that IMHO (or is that an abuse of the
DB?)

Would it help to `ALTER DATABASE set lc_collate = 'C'`,supposing that is
possible? (Oracle doesn't allow that iirc)

Thanks for any insights, pointers ...

R.