From: Raj Mathur (राज माथुर) <raju@linux-delhi.org>
To: pgsql-sql@postgresql.org
Subject: Re: Correlating Asterisk CDRs
Date: Wed, 7 Dec 2011 23:04:45 +0530
Message-ID: <201112072304.45631.raju@linux-delhi.org> (raw)
In-Reply-To: <BCCDD830-72EB-4483-819E-5E2FD30381C0@gmail.com>
References: <201112072004.29661.raju@linux-delhi.org>
<BCCDD830-72EB-4483-819E-5E2FD30381C0@gmail.com>
On Wednesday 07 Dec 2011, Brent Dombrowski wrote:
> On Dec 7, 2011, at 6:34 AM, 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.
>
> You have a non-equi join in there (cdr2.calldate >= 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
to be run with a condition of the form "cdr.calldate >=
date_trunc('day', now())", so the set of matches from cdr2 will remain
relatively constant.
However, you're right, the scope of cdr2 calldates also ought to be time
limited. Have added an "and cdr2.calldate < cdr.calldate + interval '1
day'" to the query. Thanks.
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: raju@linux-delhi.org
Subject: Re: Correlating Asterisk CDRs
In-Reply-To: <201112072304.45631.raju@linux-delhi.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox