Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group