Message-ID: <4EDF7ADE.60105@ulb.ac.be>
Date: Wed, 07 Dec 2011 15:40:30 +0100
From: Julien Cigar <jcigar@ulb.ac.be>
Organization: Belgian Biodiversity Platform
User-Agent: Mozilla/5.0 (X11; FreeBSD i386;
	rv:8.0) Gecko/20111110 Thunderbird/8.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Correlating Asterisk CDRs
References: <201112072004.29661.raju@linux-delhi.org>
In-Reply-To: <201112072004.29661.raju@linux-delhi.org>
Content-Type: multipart/mixed; boundary="------------080500080908050806010505"

This is a multi-part message in MIME format.
--------------080500080908050806010505
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Try to raise work_mem

On 12/07/2011 15:34, Raj Mathur (राज माथुर) wrote:
> Hi,
>
> 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.
>
> Regards,
>
> -- Raj


-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

--------------080500080908050806010505
Content-Type: text/x-vcard; charset=utf-8;
 name="jcigar.vcf"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="jcigar.vcf"

YmVnaW46dmNhcmQNCmZuOkp1bGllbiBDaWdhcg0KbjpDaWdhcjtKdWxpZW4NCm9yZztxdW90
ZWQtcHJpbnRhYmxlOkJlbGdpYW4gQmlvZGl2ZXJzaXR5IFBsYXRmb3JtO1VMQiAoVW5pdmVy
c2l0PUMzPUE5IExpYnJlIGRlIEJydXhlbGxlcykNCmFkcjo7OztCcnVzc2Vsczs7O0JlbGdp
dW0NCmVtYWlsO2ludGVybmV0OmpjaWdhckB1bGIuYWMuYmUNCnRlbDt3b3JrOiszMigwKTI2
NTA1NzUyDQp4LW1vemlsbGEtaHRtbDpGQUxTRQ0KdXJsOmh0dHA6Ly93d3cuYmlvZGl2ZXJz
aXR5LmJlDQp2ZXJzaW9uOjIuMQ0KZW5kOnZjYXJkDQoNCg==
--------------080500080908050806010505--