Subject: Re: Correlating Asterisk CDRs
Mime-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=utf-8
From: Brent Dombrowski <brent.dombrowski@gmail.com>
In-Reply-To: <201112072004.29661.raju@linux-delhi.org>
Date: Wed, 7 Dec 2011 09:13:04 -0800
Cc: "pgsql-sql" <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <BCCDD830-72EB-4483-819E-5E2FD30381C0@gmail.com>
References: <201112072004.29661.raju@linux-delhi.org>
To: raju@linux-delhi.org

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:

> Hi,
>=20
> 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 PLAN =
                                                                =20
> =
--------------------------------------------------------------------------=
-------------------------------------------------------------------
> Limit  (cost=3D46782.15..46782.40 rows=3D100 width=3D109) (actual =
time=3D4077.866..4078.054=20
> rows=3D100 loops=3D1)
>   ->  Sort  (cost=3D46782.15..46785.33 rows=3D1272 width=3D109) =
(actual time=3D4077.863..4077.926=20
> rows=3D100 loops=3D1)
>         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)
>               Merge Cond: (((cdr.clid)::text =3D (cdr2.clid)::text) =
AND ((cdr.dst)::text =3D=20
> "substring"((cdr2.dst)::text, 4)))
>               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)
>               ->  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)
> 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
> 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.
>=20
> Regards,
>=20
> -- Raj
> --=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
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql


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.

Brent.