(forwarded to pgsql-general after it went to Kevin Grittner alone) On 22 December 2012 22:46, Kevin Grittner <kgri...@mail.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.
