Skip site navigation (1) Skip section navigation (2)

Partial key usage

From: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Partial key usage
Date: 2004-08-22 12:56:44
Message-ID: 1093179406.1960.23.camel@retsol1 (view raw or flat)
Thread:
Lists: pgsql-novice
PostGreSQL 7.4.3

I have a table that 'translates' codes between two types. The structure
is:
 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 appears to use a sequential access according to the explain plan.
In Informix, to get around this the LIKE could be changed to address a
substring of the 'otherCode' - ie :
 ....
AND otherCode[1,3] = 'ABC'

This would then use the index.
I have tried making the PostGreSQL code:
AND SUBSTRING(otherCode FROM 1 FOR 3) = 'ABC'

But this still comes up with a sequential scan. Is there a way to force
an indexed read?

(I did prove that it was using a sequential scan by dropping the index
and retrying the query - same time - the explain plan is accurate)





Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-08-22 17:19:18
Subject: Re: Partial key usage
Previous:From: Arthur van DorpDate: 2004-08-22 10:22:34
Subject: Re: Installing PostgreSQL in a Linux Environment

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group