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