Correlating Asterisk CDRs
От | Raj Mathur (राज माथुर) |
---|---|
Тема | Correlating Asterisk CDRs |
Дата | |
Msg-id | 201112072004.29661.raju@linux-delhi.org обсуждение исходный текст |
Ответы |
Re: Correlating Asterisk CDRs
(Julien Cigar <jcigar@ulb.ac.be>)
Re: Correlating Asterisk CDRs (Brent Dombrowski <brent.dombrowski@gmail.com>) Re: Correlating Asterisk CDRs (Scott Marlowe <scott.marlowe@gmail.com>) |
Список | pgsql-sql |
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 Scanusing 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.89rows=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
В списке pgsql-sql по дате отправления: