Correlating Asterisk CDRs

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(at)kandalaya(dot)org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F