From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: [SOLVED] Re: Correlating Asterisk CDRs
Date: Wed, 7 Dec 2011 21:02:15 +0530
User-Agent: KMail/1.13.7 (Linux/3.0.0-1-686-pae; KDE/4.6.5; i686; ; )
References: <201112072004.29661.raju@linux-delhi.org>
	<4EDF7ADE.60105@ulb.ac.be>
	<201112072044.47952.raju@linux-delhi.org>
In-Reply-To: <201112072044.47952.raju@linux-delhi.org>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201112072102.15651.raju@linux-delhi.org>

On Wednesday 07 Dec 2011, Raj Mathur (=E0=A4=B0=E0=A4=BE=E0=A4=9C =E0=A4=AE=
=E0=A4=BE=E0=A4=A5=E0=A5=81=E0=A4=B0) wrote:
> On Wednesday 07 Dec 2011, Julien Cigar wrote:
> > Try to raise work_mem
>=20
> Odd, I tried adding work_mem=3D50MB / 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.

Seems that the time is constant at around 13 seconds once you go above=20
200 records or so, which is acceptable.  Thanks for your help.

Regards,

=2D- Raj

> > On 12/07/2011 15:34, Raj Mathur (=E0=A4=B0=E0=A4=BE=E0=A4=9C =E0=A4=AE=
=E0=A4=BE=E0=A4=A5=E0=A5=81=E0=A4=B0) wrote:
> > > I'm trying to correlate Call Data Records (CDRs) from two
> > > Asterisk servers, one of which uses the other for telephony.=20
> > > The data is in the tables cdr and cdr2.  With some indexes, the
> > > query and explain result are:
> > >=20
> > > 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>=3D
> > > cdr.calldate and cdr.clid=3Dcdr2.clid and
> > > cdr.dst=3Dsubstring(cdr2.dst from 4) order by cdr.calldate,
> > > cdr2.calldate, cdr.clid limit 100;
> > >=20
> > >                                                              =20
QUERY
>=20
> PLAN
>=20
> > > -----------------------------------------------------------------
> > > --
> > > ----------------------------------------------------------------
> > > --- -------
> > >=20
> > >   Limit  (cost=3D46782.15..46782.40 rows=3D100 width=3D109) (actual
> > >   time=3D4077.866..4078.054
> > >=20
> > > rows=3D100 loops=3D1)
> > >=20
> > >     ->   Sort  (cost=3D46782.15..46785.33 rows=3D1272 width=3D109)
> > >     (actual time=3D4077.863..4077.926
> > >=20
> > > rows=3D100 loops=3D1)
> > >=20
> > >           Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
> > >           Sort Method:  top-N heapsort  Memory: 42kB
> > >           ->   Merge Join  (cost=3D2.95..46733.54 rows=3D1272
> > >           width=3D109) (actual
> > >=20
> > > time=3D0.070..3799.546 rows=3D168307 loops=3D1)
> > >=20
> > >                 Merge Cond: (((cdr.clid)::text =3D
> > >                 (cdr2.clid)::text) AND ((cdr.dst)::text =3D
> > >=20
> > > "substring"((cdr2.dst)::text, 4)))
> > >=20
> > >                 Join Filter: (cdr2.calldate>=3D cdr.calldate)
> > >                 ->   Index Scan using ick1 on cdr
> > >                 (cost=3D0.00..34667.86 rows=3D208798
> > >=20
> > > width=3D43) (actual time=3D0.022..434.246 rows=3D208798 loops=3D1)
> > >=20
> > >                 ->   Index Scan using i2k1 on cdr2
> > >                 (cost=3D0.00..9960.89 rows=3D65449 width=3D88)
> > >=20
> > > (actual time=3D0.011..391.599 rows=3D240981 loops=3D1)
> > >=20
> > >   Total runtime: 4078.184 ms
> > >=20
> > > (10 rows)
> > >=20
> > > 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.
> > >=20
> > > Application
> > > -----------
> > >=20
> > > 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.


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