2007/9/5, Martin Langhoff <[EMAIL PROTECTED]>: > Hi! > > I am having a bit of trouble with indexes, locales and LIKE queries. > > Background > ---------- > > Using Pg8.1/8.2 on a utf-8 database, 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'. > > From http://www.postgresql.org/docs/8.1/static/indexes-types.html : > > 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. > > 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, as we support MySQL, Pg, MSSQL, > Oracle. 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.
no no. just create ordinary btree indexes with text_pattern_ops, _always_, disregarding the locale. it should not hurt. create index i1 on t1 ( text1 text_pattern_ops ); > > New PostgreSQL installs on modern linuxen like Ubuntu default to non-C > locales, which makes this more of an issue going forward. > > 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 > -- > ----------------------------------------------------------------------- > Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > NZ: +64(4)916-7224 MOB: +64(21)364-017 UK: 0845 868 5733 ext 7224 > Make things as simple as possible, but no simpler - Einstein > ----------------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Filip Rembiałkowski ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
