Re: Correlating Asterisk CDRs - Mailing list pgsql-sql

On Wednesday 07 Dec 2011, Julien Cigar wrote:
> Try to raise work_mem

Odd, I tried adding work_mem=50MB / 256MB / 1024MB into postgres.conf
and the times actually went up to over 12 seconds.  Leaving it commented
results in the 4-second time originally posted.

Regards,

-- Raj

> On 12/07/2011 15:34, 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.
> >
> > 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.

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