I am looking for some ideas to speed up this query.
I need to look at the first 6 digits of a clients phone number and compare it with the npanxx # in the tiers table.
Then find out if they are in Tier A, B or C.
select count(*) as a
from call_results_fixed cr, tiers t
where cr.start_time between '07/22/2002 19:30' and '07/22/2002 21:30'
and cr.project_id = 11
and substring(cr.phonenum from 1 for 6) = t.npanxx
and t.tier = 'A' ;
Here are the results of explain analyze.
NOTICE: QUERY PLAN:
Aggregate (cost=38021.71..38021.71 rows=1 width=29) (actual time=42412.76..4241
2.76 rows=1 loops=1)
-> Nested Loop (cost=0.00..38021.71 rows=1 width=29) (actual time=42412.74..
42412.74 rows=0 loops=1)
-> Seq Scan on call_results_fixed (cost=0.00..37488.47 rows=24 width=1
9) (actual time=42412.73..42412.73 rows=0 loops=1)
-> Seq Scan on tiers t (cost=0.00..22.50 rows=5 width=10)
Total runtime: 42412.95 msec
EXPLAIN
Its the substring function that slows things down so much. Is there a better way to compare these string values?
substr(cr.phonenum, 1,6) is the same speed. (and according to the docs, basicly the same function)
TIA
Chad
