Re: Correlating Asterisk CDRs
От | Raj Mathur (राज माथुर) |
---|---|
Тема | Re: Correlating Asterisk CDRs |
Дата | |
Msg-id | 201112072304.45631.raju@linux-delhi.org обсуждение исходный текст |
Ответ на | Re: Correlating Asterisk CDRs (Brent Dombrowski <brent.dombrowski@gmail.com>) |
Список | pgsql-sql |
On Wednesday 07 Dec 2011, Brent Dombrowski wrote: > On Dec 7, 2011, at 6:34 AM, Raj Mathur (राज माथुर) wrote: > > 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. > > 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. Seen. At the moment (and in the foreseeable future) the query is going to be run with a condition of the form "cdr.calldate >= date_trunc('day', now())", so the set of matches from cdr2 will remain relatively constant. However, you're right, the scope of cdr2 calldates also ought to be time limited. Have added an "and cdr2.calldate < cdr.calldate + interval '1 day'" to the query. Thanks. 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
В списке pgsql-sql по дате отправления: