Hi! Background: Using Pg8.1/8.2 on a utf-8 database, I found out that my left-anchored LIKE clauses were forcing a full table scan instead of using the index. After a bit of digging, I found that Pg can only use the "normal" index for left-anchored LIKE queries if locale is 'C'. "The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your server does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries." http://www.postgresql.org/docs/8.1/static/indexes-types.html What I think I need to do: As I have a Pg install where the locale is already en_US.UTF-8, and the database already exists, is there a DB-scoped way of controlling the locale? I think the index usage noted above is affected by lc_ctype but I could be wrong. I really don't want to go down the "rebuild your pgcluster" path as outlined here http://archives.postgresql.org/pgsql-hackers/2007-02/msg00992.php ;-) Is there a better way? In this specific install I can create the additional index. However, this needs a general fix for Moodle, which has an abstract DB schema handling (we support MySQL, Pg, MSSQL, Oracle) and the whole thing of figuring out what the locale is and whether to add magical additional indexes just for Pg makes me look like a loony. See the discussion with Eloy (maintainer of the schema abstraction layer) at http://moodle.org/mod/forum/discuss.php?d=78738#p350512 login as "guest" to avoid registration. cheers, martin ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
