Re: Correlating Asterisk CDRs

On Dec 7, 2011, at 6:34 AM, Raj Mathur (राज माथुर) wrote:

> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

You have a non-equi join in there (cdr2.calldate >= cdr.calldate). I would try to get rid of that. It's increasing the number of rows in the result set and will only get worse as your data set grows.

Brent.