From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: "pgsql-sql" <pgsql-sql@postgresql.org>
Subject: Correlating Asterisk CDRs
Date: Wed, 7 Dec 2011 20:04:29 +0530
User-Agent: KMail/1.13.7 (Linux/3.0.0-1-686-pae; KDE/4.6.5; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201112072004.29661.raju@linux-delhi.org>

Hi,

I'm trying to correlate Call Data Records (CDRs) from two Asterisk
servers, one of which uses the other for telephony.  The data is in
the tables cdr and cdr2.  With some indexes, the query and explain
result are:

explain analyse select cdr.calldate, cdr2.calldate,
(cdr2.calldate-cdr.calldate) as rtdur, cdr.clid, cdr.dst, cdr2.src,
cdr2.dst, cdr2.dstchannel, cdr2.lastapp, cdr2.duration,
cdr2.disposition from cdr, cdr2 where cdr2.calldate >= cdr.calldate
and cdr.clid=cdr2.clid and cdr.dst=substring(cdr2.dst from 4) order by
cdr.calldate, cdr2.calldate, cdr.clid limit 100;

                                                             QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=46782.15..46782.40 rows=100 width=109) (actual time=4077.866..4078.054 
rows=100 loops=1)
   ->  Sort  (cost=46782.15..46785.33 rows=1272 width=109) (actual time=4077.863..4077.926 
rows=100 loops=1)
         Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
         Sort Method:  top-N heapsort  Memory: 42kB
         ->  Merge Join  (cost=2.95..46733.54 rows=1272 width=109) (actual 
time=0.070..3799.546 rows=168307 loops=1)
               Merge Cond: (((cdr.clid)::text = (cdr2.clid)::text) AND ((cdr.dst)::text = 
"substring"((cdr2.dst)::text, 4)))
               Join Filter: (cdr2.calldate >= cdr.calldate)
               ->  Index Scan using ick1 on cdr  (cost=0.00..34667.86 rows=208798 
width=43) (actual time=0.022..434.246 rows=208798 loops=1)
               ->  Index Scan using i2k1 on cdr2  (cost=0.00..9960.89 rows=65449 width=88) 
(actual time=0.011..391.599 rows=240981 loops=1)
 Total runtime: 4078.184 ms
(10 rows)

Is there any way to make this query faster?  I already have an index
i2k1 on substring(cdr2.dst from 4), which is being used.

Application
-----------

I'm looking for all caller records in cdr2 that have the same callerid
(clid) and destination (dst) and were started on cdr2 after they were
started on cdr.  cdr2.dst is the same as cdr.dst but with a
3-character prefix.

Regards,

-- Raj
-- 
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F