Re: Partial key usage

Steve Tucknott <steve@retsol.co.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