Using a compound primary key

Hi all,

I have a table containing tariff information. It has a primary key (and therefore unique index) of (tariff_type varchar(5),prefix varchar(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 made a phone call to +4412345678 using tariff_type abc, then I would want to retrieve the record abc,441,$y and not the record abc,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'::character varying) 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.23 rows=1 width=31)
-> Sort (cost=5.23..5.23 rows=1 width=31)
Sort Key: prefix
-> Index Scan using tariff_ix2 on tariff (cost=0.00..5.22 rows=1 width=31)
Index Cond: ((tariff_type = 'UIA'::character varying) 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 performance increase but it's still not as cost-efficient as using the primary index. Would it be more cost effective to do a bunch of individual queries for each length of prefix until I find one that matches? The average length of a prefix would probably 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