Re: Correlating Asterisk CDRs - Mailing list pgsql-sql

On Thursday 08 Dec 2011, Scott Marlowe wrote:
> 2011/12/7 Raj Mathur (राज माथुर) <raju@linux-delhi.org>:
> >                                                             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)
>
> Two things to look at here.  First is that the estimation of rows
> expected and returned vary by a factor over over 100, which means the
> query planner may be making suboptimal choices in terms of the plan
> it is running.  If increasing stats target on the target columns in
> the query helps, then that's worth trying.  Raise it and re-analyze
> and see if you get a closer estimate.  To test if the merge join is
> the best choice or not, you can use the set enable_xxx for it (in
> this case set enable_mergejoin=off) and then run the query again
> through explain analyze and see if the performance gets any better.

Fixed the first -- all it needed was a vacuum analyse, and the performance
improved by 50%.  Enabling/disabling mergejoin doesn't seem to make any
difference to the timing.  However, after the vacuum analyse the planner
is now using:
Limit  (cost=37499.24..37502.08 rows=1138 width=109) (actual time=6355.308..6709.661
rows=168307 loops=1)  ->  Sort  (cost=37499.24..37502.08 rows=1138 width=109) (actual time=6355.304..6491.595
rows=168307 loops=1)        Sort Key: cdr.calldate, cdr2.calldate, cdr.clid        Sort Method:  quicksort  Memory:
45211kB       ->  Merge Join  (cost=34720.94..37441.47 rows=1138 width=109) (actual  
time=3438.318..5853.947 rows=168307 loops=1)              Merge Cond: (((cdr.dst)::text =
("substring"((cdr2.dst)::text,4))) AND  
((cdr.clid)::text = (cdr2.clid)::text))              Join Filter: (cdr2.calldate >= cdr.calldate)              ->  Sort
(cost=26987.11..27509.10 rows=208798 width=43) (actual  
time=2631.166..2833.926 rows=208748 loops=1)                    Sort Key: cdr.dst, cdr.clid                    Sort
Method: quicksort  Memory: 19696kB                    ->  Seq Scan on cdr  (cost=0.00..8537.98 rows=208798 width=43)  
(actual time=0.009..211.330 rows=208798 loops=1)              ->  Sort  (cost=7684.78..7848.41 rows=65449 width=89)
(actual 
time=807.031..991.649 rows=240981 loops=1)                    Sort Key: ("substring"((cdr2.dst)::text, 4)), cdr2.clid
                Sort Method:  quicksort  Memory: 9889kB                    ->  Seq Scan on cdr2  (cost=0.00..2449.49
rows=65449width=89)  
(actual time=0.021..125.630 rows=65449 loops=1)Total runtime: 6823.029 ms

Can you see any place here where adding indexes may help?

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