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/s...xes-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...2/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

Martin Langhoff escribió:

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

Not really.

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

You are right and Eloy is wrong on that discussion. There is not
anything the DB can do to use the regular index if the locale is not C
for LIKE queries. There are good reasons for this. There's not much
option beyond creating the pattern_ops index.

--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Most hackers will be perfectly comfortable conceptualizing users as entropy
sources, so let's move on." (Nathaniel Smith)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

> Martin Langhoff escribió:
>
>> 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.
>
> You are right and Eloy is wrong on that discussion. There is not
> anything the DB can do to use the regular index if the locale is not C
> for LIKE queries. There are good reasons for this. There's not much
> option beyond creating the pattern_ops index.

Indeed *all* indexes are magical additional things added just for the one
database. There's not any standard definition of what indexes you'll need for
all databases out there. Indexes aren't even in the SQL standard because
they're part of performance tuning for each individual database engine.


--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

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