Re: Partial key usage

Steve Tucknott <steve(at)retsol(dot)co(dot)uk> writes:
> recno | integer | not null default
> nextval('public.kah_kahxlate_recno_seq'::text)
> kahcode | character(25) | not null
> othercodetype | character varying(40) | not null
> othercode | character varying(40) | not null
> othercoden | numeric(20,0) |
> Indexes:
> "kah_kahxlate_cpk" primary key, btree (recno)
> "ka_kahxlate_2" btree (othercodetype, othercode)
> "kah_kahxlate_1" btree (kahcode, othercodetype)

> What can happen is that the 'othercode' can be partial - so can be
> accessed with LIKE - ie
> SELECT kahCode FROM kah_kahXlate
> WHERE otherCodeType = 'FRED'
> AND otherCode LIKE 'ABC%';

This should be able to use an index on (othercodetype, othercode).
If it's not, I would speculate that your database collation is not C
(check "SHOW LC_COLLATE"). Non-C locales usually sort in an order
that isn't compatible with pattern matching.

You can either re-initdb in C locale, or make a specialized index
using LIKE-compatible comparison operators. See the docs about
specialized index operator classes.

regards, tom lane