On 12/07/2011 16:14, Raj Mathur (राज माथुर) wrote:
On Wednesday 07 Dec 2011, Julien Cigar wrote:Try to raise work_memOdd, 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, -- RajOn 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; QUERYPLAN------------------------------------------------------------------- ------------------------------------------------------------------- ------- 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.
<<attachment: jcigar.vcf>>
-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
