On Wednesday 07 Dec 2011, 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.
Seems that the time is constant at around 13 seconds once you go above 200 records or so, which is acceptable. Thanks for your help. 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. -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
