From: Julien Cigar <jcigar@ulb.ac.be>
To: pgsql-sql@postgresql.org
Subject: Re: Correlating Asterisk CDRs
Date: Wed, 07 Dec 2011 16:25:25 +0100
Message-ID: <4EDF8565.3000005@ulb.ac.be> (raw)
In-Reply-To: <201112072044.47952.raju@linux-delhi.org>
References: <201112072004.29661.raju@linux-delhi.org>
<4EDF7ADE.60105@ulb.ac.be>
<201112072044.47952.raju@linux-delhi.org>
On 12/07/2011 16:14, Raj Mathur (राज माथुर) wrote:
> On Wednesday 07 Dec 2011, Julien Cigar wrote:
>> Try to raise work_mem
>
> Odd, I tried adding work_mem=50MB / 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.
sorry I replied too fast.. There is no external disk merge so increasing
work_mem is useless. Problem is the "merge join". How big is the table?
Are the statistics up to date?
>
> Regards,
>
> -- Raj
>
>> On 12/07/2011 15:34, 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.
>>>
>>> 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.
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:
[text/x-vcard] jcigar.vcf (304B, ../4EDF8565.3000005@ulb.ac.be/2-jcigar.vcf)
download | inline:
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
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: jcigar@ulb.ac.be
Subject: Re: Correlating Asterisk CDRs
In-Reply-To: <4EDF8565.3000005@ulb.ac.be>
* 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