Re: query optimizer questions

> Thanks. How do I set the locale?

Short answer:

bash$ LANG=C initdb

Longer answer:

I find locale support in PostgreSQL confusing. The locale of the current user at the time of initialising the database determines the collating order (e.g. is 'a' < 'A'). This in turns affect things like indexing (as described below). The locale of the account that is running the current postmaster defines format conversions (e.g. the currency symbol in to_char(1,'L99')). The locale of the current process requesting a query defines how the user expects to see the result, and is (of course) ignored by PostgreSQL.

To make matters worse: On Red Hat 7.1 (US boxed set) the default locale is set in /etc/sysconf/i18n to en_US, i.e. American English. Fine, except some Americans may be surprised to learn that 'A' > 'a'. When you start the postgresql service without doing an initdb first, the script will helpfully copy i18n to ~postgres/initdb.i18n so you have a record of the locale (and can re-set it). Except it never uses this file. In fact, the postmaster service runs in the default locale *for root*. A previous version of RH did go through the trouble of sourcing initdb.i18n in .bash_profile, but it does nothing for postmaster which is started as su -l postgres -s /bin/sh ...

In order to start postmaster on RH7.1 with a locale that is different from the default (actually: different from root) you should create ~postgres/.profile with the content

PGLIB=/usr/lib/pgsql
PGDATA=/var/lib/pgsql/data
export PGLIB PGDATA
[ -f $PGDATA/../initdb.i18n ] && source $PGDATA/../initdb.i18n

You may, of course, have to change the path. You may want to add that last line to your ~postgres/.bash_profile as well, in order to avoid future confusion....

If you dont have initdb.i18n, just delete the content of the data directory and start the postgres service. The result should look something like

LANG="en_US"
export LANG LC_ALL LC_CTYPE LC_COLLATE LC_NUMERIC LC_CTYPE LC_TIME

for a default installation, but of course you want LANG="C"

Hope this makes sense to somebody....e-mail if I've managed to confuse anybody, including myself :-)

Allan.