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