Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7469F9FB5DF for ; Tue, 27 Mar 2007 07:43:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 65154-01 for ; Tue, 27 Mar 2007 07:43:35 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 9F0C59FB5AF for ; Tue, 27 Mar 2007 07:43:35 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id k3so1713142ugf for ; Tue, 27 Mar 2007 03:43:34 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HRPg9EDf5ufAUmynExS8grP8+clvEaTUr7t3V0LEm+m4Ox7yppDdlYFP4XCsv+1LwksQ76VEHq8wZo+7gCscH9OYOhlba72GWozR6Diyc3CkPE4GuziOE4yChWAdTeml75BuhV+5YqfN3UwAgIaPHOzZuu37RdJt6GkYjKWRfxE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=fhRclVvAFh0XQgTrTmvpB6TQ9qsK3xShDBzEmlWxFdZQRUpgbWmmc15rr0rsNzkUWDd6XVqRwEH64DirmKINwbM3xlakp+5bQ6AF844TZl4b+IJjjyH0+GyC6LU9+CugjjGyNnxKzeBIG4k4xrr1iV1l46avDfexjXoTsrPXi44= Received: by 10.66.243.4 with SMTP id q4mr783563ugh.1174992211927; Tue, 27 Mar 2007 03:43:31 -0700 (PDT) Received: by 10.66.240.14 with HTTP; Tue, 27 Mar 2007 03:43:31 -0700 (PDT) Message-ID: <7e4ba9550703270343o4dadf1c0sdcf9f06eba64ed00@mail.gmail.com> Date: Tue, 27 Mar 2007 16:13:31 +0530 From: "Gauri Kanekar" To: Ragnar Subject: Re: Nested Loop Cc: pgsql-performance@postgresql.org In-Reply-To: <1174926153.1153.31.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_60286_30411517.1174992211840" References: <7e4ba9550703260504u6e9402ferd958620c79e9640a@mail.gmail.com> <20070326140549.GA9322@winnie.fuhr.org> <7e4ba9550703260803l16b86ef0w42182ef762837db8@mail.gmail.com> <1174926153.1153.31.camel@localhost.localdomain> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/400 X-Sequence-Number: 23805 ------=_Part_60286_30411517.1174992211840 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, here is the query SELECT rs.id AS sid, rs.name AS sname, rc.id AS campid, rc.name AS campname= , rc.rev_type AS revtype, rc.act_type AS actntype, ra.id AS advid, ra.name AS advname, rpt_chn.id AS chanid, rpt_chn.name AS channame, rpt_cre.dn AS dn, SUM(rm.imdel) AS impression, SUM(rm.cdel) AS click, rd.sqldate AS date FROM rm, rn CROSS JOIN rd, ra, rs, rc, rpt_chn, rpt_cre WHERE rm.date_key =3D rd.key AND rm.net_key =3D rn.key AND rm.adv_key =3D ra.key AND rm.camp_key = =3D rc.key AND rm.s_key =3D rs.key AND rm.chn_key =3D rpt_chn.key AND rm.cre_ke= y =3D rpt_cre.key AND ra.name !=3D 'SYSTEM' AND rd.sqldate BETWEEN '12/1/2006' AN= D '12/30/2006' AND ( rn.id IN ( 607 ) ) GROUP BY rd.sqldate, rs.id, rs.name, ra.id, ra.name, rc.id, rc.name, rc.rev_type, rc.act_type, rpt_chn.id, rpt_chn.name, rpt_cre.dn; On 3/26/07, Ragnar wrote: > > On m=E1n, 2007-03-26 at 20:33 +0530, Gauri Kanekar wrote: > > you did not show your query, nor did you answer whather you had vacuumed > and analyzed. > > > enable_seqscan =3D off > > why this? this is unlikely to help > > > > > > QUERY PLAN > > ... > > -> Nested Loop > > (cost=3D0.00..1104714.83 rows=3D6801 width=3D44) (actual > > time=3D1820.153..229779.814 rows=3D10945938 loops=3D1) > > the estimates are way off here. you sure you have analyzed? > > gnari > > > > > --=20 Regards Gauri ------=_Part_60286_30411517.1174992211840 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi,
 
here is the query
 
SELECT rs.id AS sid, rs.name AS sname, rc.id AS campid, = rc.name AS campname, rc.rev_type AS revtype,= rc.act_type AS actntype, ra.id AS advid, ra.name AS advname, rpt_chn.id AS chanid, rpt_chn.name AS ch= anname, rpt_cre.dn AS dn, SUM(rm.imdel) AS impression, SUM(rm.cdel) AS clic= k,=20 rd.sqldate AS date FROM rm, rn CROSS JOIN rd, ra, rs, rc, rpt_chn, rpt_cre = WHERE rm.date_key =3D rd.key AND rm.net_key =3D rn.key AND rm.adv_key =3D r= a.key AND rm.camp_key =3D rc.key AND rm.s_key =3D rs.key AND rm.chn_key =3D= rpt_chn.key AND=20 rm.cre_key =3D rpt_cre.key AND ra.name !=3D = 'SYSTEM' AND rd.sqldate BETWEEN '12/1/2006' AND '12/30/= 2006' AND ( rn.id IN ( 607 ) ) GROUP BY rd= .sqldate , rs.id, rs.name, ra.id, ra.name, rc.id, rc.name<= /a>, rc.rev_type , rc.act_type, rpt_chn.id, rpt_chn.name, rpt_cre.dn;

 
 
On 3/26/07, = Ragnar <gnari@hive.is> wrote= :
On m=E1n, 2007-03-26 at 20:33 +0= 530, Gauri Kanekar wrote:

you did not show your query, nor did you a= nswer whather you had vacuumed
and analyzed.

> enable_seqscan =3D off

why this? this = is unlikely to help


>
> QUERY PLAN
> ...
>&= nbsp;           &nbs= p;            &= nbsp;        ->  Neste= d Loop
> (cost=3D0.00..1104714.83 rows=3D6801 width=3D44) (actual
> time=3D1820.153..229779.814 rows= =3D10945938 loops=3D1)

the estimates are way off here. you sure you = have analyzed?

gnari

>




--
Regards
Gauri=20 ------=_Part_60286_30411517.1174992211840--