From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: Correlating Asterisk CDRs
Date: Thu, 8 Dec 2011 07:58:11 +0530
User-Agent: KMail/1.13.7 (Linux/3.0.0-1-686-pae; KDE/4.6.5; i686; ; )
References: <201112072004.29661.raju@linux-delhi.org>
	<CAOR=d=2+-jRy1ZQQ5bbaL9RLdE_sS0q=bCRMKhCdoKQFpP5fLw@mail.gmail.com>
In-Reply-To: 
 <CAOR=d=2+-jRy1ZQQ5bbaL9RLdE_sS0q=bCRMKhCdoKQFpP5fLw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201112080758.12110.raju@linux-delhi.org>

On Thursday 08 Dec 2011, Scott Marlowe wrote:
> 2011/12/7 Raj Mathur (=E0=A4=B0=E0=A4=BE=E0=A4=9C =E0=A4=AE=E0=A4=BE=E0=
=A4=A5=E0=A5=81=E0=A4=B0) <raju@linux-delhi.org>:
> >                                                             QUERY
> > PLAN
> > ------------------------------------------------------------------
> > -------------------------------------------------------------------
> > -------- Limit  (cost=3D46782.15..46782.40 rows=3D100 width=3D109)
> > (actual time=3D4077.866..4078.054 rows=3D100 loops=3D1)
> >   ->  Sort  (cost=3D46782.15..46785.33 rows=3D1272 width=3D109) (actual
> > time=3D4077.863..4077.926 rows=3D100 loops=3D1)
> >         Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
> >         Sort Method:  top-N heapsort  Memory: 42kB
> >         ->  Merge Join  (cost=3D2.95..46733.54 rows=3D1272 width=3D109)
> > (actual time=3D0.070..3799.546 rows=3D168307 loops=3D1)
>=20
> 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=3Doff) and then run the query again
> through explain analyze and see if the performance gets any better.

=46ixed 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=3D37499.24..37502.08 rows=3D1138 width=3D109) (actual time=3D=
6355.308..6709.661=20
rows=3D168307 loops=3D1)
   ->  Sort  (cost=3D37499.24..37502.08 rows=3D1138 width=3D109) (actual ti=
me=3D6355.304..6491.595=20
rows=3D168307 loops=3D1)
         Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
         Sort Method:  quicksort  Memory: 45211kB
         ->  Merge Join  (cost=3D34720.94..37441.47 rows=3D1138 width=3D109=
) (actual=20
time=3D3438.318..5853.947 rows=3D168307 loops=3D1)
               Merge Cond: (((cdr.dst)::text =3D ("substring"((cdr2.dst)::t=
ext, 4))) AND=20
((cdr.clid)::text =3D (cdr2.clid)::text))
               Join Filter: (cdr2.calldate >=3D cdr.calldate)
               ->  Sort  (cost=3D26987.11..27509.10 rows=3D208798 width=3D4=
3) (actual=20
time=3D2631.166..2833.926 rows=3D208748 loops=3D1)
                     Sort Key: cdr.dst, cdr.clid
                     Sort Method:  quicksort  Memory: 19696kB
                     ->  Seq Scan on cdr  (cost=3D0.00..8537.98 rows=3D2087=
98 width=3D43)=20
(actual time=3D0.009..211.330 rows=3D208798 loops=3D1)
               ->  Sort  (cost=3D7684.78..7848.41 rows=3D65449 width=3D89) =
(actual=20
time=3D807.031..991.649 rows=3D240981 loops=3D1)
                     Sort Key: ("substring"((cdr2.dst)::text, 4)), cdr2.clid
                     Sort Method:  quicksort  Memory: 9889kB
                     ->  Seq Scan on cdr2  (cost=3D0.00..2449.49 rows=3D654=
49 width=3D89)=20
(actual time=3D0.021..125.630 rows=3D65449 loops=3D1)
 Total runtime: 6823.029 ms

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

Regards,

=2D- Raj
=2D-=20
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F