Using a compound primary key - Mailing list pgsql-sql

Hi all,

I have a table containing tariff information. It has a primary key (and therefore unique index) of (tariff_type
varchar(5),prefixvarchar(12)) where tariff_type is a set of rates assigned to dialling prefixes. 
e.g.
tariff_type    prefix    rate
abc        44    $x
abc        441    $y
abc        61    $z
def        44    $a
def        441    $b
def        61    $c

and so on.

For a known tariff_type, I need to find the rate that has the longest matching dialling prefix. In the data above, if I
madea phone call to +4412345678 using tariff_type abc, then I would want to retrieve the record abc,441,$y and not the
recordabc,44,$x. I do this currently by dividing up the phone number and using this query: 

select * from tariff
where tariff_type = 'UIA'
and prefix in ('44','441','4412','44123','441234','4412345','44123456')
order by prefix desc limit 1;

The query doesn't use the primary key index as I might expect:
Limit  (cost=98.88..98.88 rows=1 width=31)  ->  Sort  (cost=98.88..98.89 rows=7 width=31)        Sort Key: prefix
->  Seq Scan on tariff  (cost=0.00..98.78 rows=7 width=31)              Filter: ((tariff_type = 'UIA'::character
varying)AND ((prefix = '44'::character varying) OR (prefix = '441'::character varying) OR (prefix = '4412'::character
varying)OR (prefix = '44123'::character varying) OR (prefix = '441234'::character varying) OR (prefix =
'4412345'::charactervarying) OR (prefix = '44123456'::character varying))) 

If I specify both parts of the key then it will, of course, use the index and cost very little:

select * from tariff
where tariff_type = 'UIA' and prefix = '441'
order by prefix desc limit 1;                                                                            QUERY PLAN
                                             
--------------------------------------------------------------------------------------------------------------Limit
(cost=5.23..5.23rows=1 width=31)  ->  Sort  (cost=5.23..5.23 rows=1 width=31)        Sort Key: prefix        ->  Index
Scanusing tariff_ix2 on tariff  (cost=0.00..5.22 rows=1 width=31)              Index Cond: ((tariff_type =
'UIA'::charactervarying) AND (prefix = '441'::character varying)) 

I have used this exact scenario using an Informix database and the query planner is able to use the index:

QUERY:
------
select *
from tariff where tariff_type = 'SIL18'
and (prefix = '44' or prefix = '441' or prefix = '4412'
or prefix = '44123' or prefix = '441234' or prefix = '4412345'
or prefix = '44123456' ) order by prefix desc

Estimated Cost: 1
Estimated # of Rows Returned: 1

1) informix.tariff: INDEX PATH
   (1) Index Keys: tariff_type prefix   (Key-First)       Lower Index Filter: informix.old_tariff.tariff_type = 'SIL18'
      Key-First Filters:  (((((((informix.old_tariff.prefix = '44' OR informix.ol 
d_tariff.prefix = '441' ) OR informix.old_tariff.prefix = '4412' ) OR informix.old_
tariff.prefix = '44123' ) OR informix.old_tariff.prefix = '441234' ) OR informix.ol
d_tariff.prefix = '4412345' ) OR informix.old_tariff.prefix = '44123456' ) )

Is there a way I can achieve the same result using PostgreSQL 7.3.2? I can add another index just on prefix and get a
performanceincrease but it's still not as cost-efficient as using the primary index. Would it be more cost effective to
doa bunch of individual queries for each length of prefix until I find one that matches? The average length of a prefix
wouldprobably be around 3 digits and I would need to start at 8 digits and work back to cover all possibilities. 

Thanks for any advice,
David Witham