(forwarded to pgsql-general after it went to Kevin Grittner alone)
On 22 December 2012 22:46, Kevin Grittner <kgrittn@xxxxxxxx> 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 indexHave you run VACUUM ANALYZE with the index and data in place (as
> 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')
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.
