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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-08-22 17:19:18 | Re: Partial key usage |
Previous Message | Arthur van Dorp | 2004-08-22 10:22:34 | Re: Installing PostgreSQL in a Linux Environment |