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: Re: Correlating Asterisk CDRs
Date: Wed, 7 Dec 2011 23:04:45 +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>
	<BCCDD830-72EB-4483-819E-5E2FD30381C0@gmail.com>
In-Reply-To: <BCCDD830-72EB-4483-819E-5E2FD30381C0@gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201112072304.45631.raju@linux-delhi.org>

On Wednesday 07 Dec 2011, Brent Dombrowski wrote:
> On Dec 7, 2011, at 6:34 AM, 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.  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
> >                                                             QUERY=20
PLAN
> >=20
> > -------------------------------------------------------------------
> > -------------------------------------------------------------------
> > ------- Limit  (cost=3D46782.15..46782.40 rows=3D100 width=3D109) (actu=
al
> > time=3D4077.866..4078.054 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=20
> >               (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=20
> >               (cost=3D0.00..9960.89 rows=3D65449 width=3D88)
> >=20
> > (actual time=3D0.011..391.599 rows=3D240981 loops=3D1)
> > Total runtime: 4078.184 ms
> > (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
> You have a non-equi join in there (cdr2.calldate >=3D 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=20
to be run with a condition of the form "cdr.calldate >=3D=20
date_trunc('day', now())", so the set of matches from cdr2 will remain=20
relatively constant.

However, you're right, the scope of cdr2 calldates also ought to be time=20
limited.  Have added an "and cdr2.calldate < cdr.calldate + interval '1=20
day'" to the query.  Thanks.

Regards,

=2D- Raj
=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