Re: Partial key usage

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
Cc: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Partial key usage
Date: 2004-08-22 17:19:18
Message-ID: 6092.1093195158@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Eyinagho Newton 2004-08-23 10:32:32 Re: Installing PostgreSQL in a Linux Environment
Previous Message Steve Tucknott 2004-08-22 12:56:44 Partial key usage