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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
