Very slow query

From: "Chad Thompson" <chad(at)weblinkservices(dot)com>
To: "pgsql-novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Very slow query
Date: 2002-07-30 20:20:00
Message-ID: 005901c23806$7c6db900$32021aac@chad
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Berkus 2002-07-30 21:03:11 Re: Very slow query
Previous Message Daniel L Banta 2002-07-30 19:33:31 initdb initialization problem