From abumushayeed@yahoo.com Mon May 7 20:40:45 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F36E89FB9CA for ; Mon, 7 May 2007 17:40:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 42455-06 for ; Mon, 7 May 2007 17:40:39 -0300 (ADT) X-Greylist: delayed 00:06:40.082164 by SQLgrey-1.7.5 Received: from web57103.mail.re3.yahoo.com (web57103.mail.re3.yahoo.com [216.252.111.116]) by postgresql.org (Postfix) with SMTP id A69FA9FB905 for ; Mon, 7 May 2007 17:40:41 -0300 (ADT) Received: (qmail 7589 invoked by uid 60001); 7 May 2007 20:33:59 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=umeh9iH3CKMYiUBOZCaSnG5fUa09JV36PZMZvL09yXtvwHlArDjImA92xn6qTAUXLxkgV+SI4pmaZHL3df7DHbxcqmjgr5Zo8oCz6WnOfH5Ds+LP19/AAMJkK/TsRFQuYn6lDQI+Y7ZepcBn9HNGD2EsW0yVrPoowV5L99U/8kc=; X-YMail-OSG: AM2L9SQVM1nJiyzk.uc_0DF9GXWMxIsq9NgxZ8c8AMiZUfVrkVGJN0zsItQO28xTu4Z1TcPUAfEALYo4XJUYLr8WLnnRUBeM.Rs2 Received: from [64.237.4.140] by web57103.mail.re3.yahoo.com via HTTP; Mon, 07 May 2007 13:33:59 PDT Date: Mon, 7 May 2007 13:33:59 -0700 (PDT) From: Abu Mushayeed Subject: Plannner favoring nested loop To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1421557891-1178570039=:7334" Content-Transfer-Encoding: 8bit Message-ID: <888904.7334.qm@web57103.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.117 tagged_above=0 required=5 tests=AWL=0.864, BAYES_00=-2.599, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_40_50=0.496, HTML_MESSAGE=0.001 X-Spam-Level: ** X-Archive-Number: 200705/51 X-Sequence-Number: 25371 --0-1421557891-1178570039=:7334 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello, I am trying to determine if I can make the planner see that nested loop is very expensive while it is trying to do an plan for a query and not pick that route. Is there a way to this in postgres? I know about the "set enable..." commands. I was wondering if I can do it without the hints. Thanks Abu --------------------------------- Ahhh...imagining that irresistible "new car" smell? Check outnew cars at Yahoo! Autos. --0-1421557891-1178570039=:7334 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hello,
 
I am trying to determine if I can make the planner see that nested loop is very expensive while it is trying to do an plan for a query and not pick that route.
 
Is there a way to this in postgres? I know about the "set enable..." commands. I was wondering if I can do it without the hints.
 
Thanks
Abu


Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos. --0-1421557891-1178570039=:7334-- From tgl@sss.pgh.pa.us Mon May 7 21:37:40 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1B8679FBA5B for ; Mon, 7 May 2007 18:37:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 51871-08 for ; Mon, 7 May 2007 18:37:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B983B9FB96B for ; Mon, 7 May 2007 18:37:34 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l47LbXnF019020; Mon, 7 May 2007 17:37:33 -0400 (EDT) To: Abu Mushayeed cc: pgsql-admin@postgresql.org Subject: Re: Plannner favoring nested loop In-reply-to: <888904.7334.qm@web57103.mail.re3.yahoo.com> References: <888904.7334.qm@web57103.mail.re3.yahoo.com> Comments: In-reply-to Abu Mushayeed message dated "Mon, 07 May 2007 13:33:59 -0700" Date: Mon, 07 May 2007 17:37:33 -0400 Message-ID: <19019.1178573853@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/52 X-Sequence-Number: 25372 Abu Mushayeed writes: > I am trying to determine if I can make the planner see that nested loop is very expensive while it is trying to do an plan for a query and not pick that route. If it's doing that when it shouldn't, it's probably because it's underestimated the number of rows in the outer relation. Look to your stats settings. regards, tom lane From abumushayeed@yahoo.com Mon May 7 22:30:08 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BB6D39FB1C3 for ; Mon, 7 May 2007 19:30:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 20773-10 for ; Mon, 7 May 2007 19:30:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web57105.mail.re3.yahoo.com (web57105.mail.re3.yahoo.com [216.252.111.118]) by postgresql.org (Postfix) with SMTP id 501D19FB715 for ; Mon, 7 May 2007 19:30:04 -0300 (ADT) Received: (qmail 31943 invoked by uid 60001); 7 May 2007 22:30:02 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=C32asEQxDaLXjNhPObVwER1YhdLFOwfm6cqI5OyY6ZnksH5qppcVqDoBCtPfqx9KhE85bXWgMb9u5t/L+TVKesFOHo2GYZDQ6uoD1neLzUR74sa/4Ku+Gk3iFJufrEWxkJamNP3p7ys5J2BBkYZpBOVziH47kirPR0Jl6RLCmUk=; X-YMail-OSG: ilXROQcVM1nTK_lBUjJfhfLmRaHEUjMT6dW5g0pxAFnDWjJvK4Gg0IpSx.Du.7w0ximg8xLJcN1PCLsk7DJl4WtUYIWKFsYgn.eS Received: from [64.237.4.140] by web57105.mail.re3.yahoo.com via HTTP; Mon, 07 May 2007 15:30:01 PDT Date: Mon, 7 May 2007 15:30:01 -0700 (PDT) From: Abu Mushayeed Subject: Re: Plannner favoring nested loop To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <19019.1178573853@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1303462705-1178577001=:30909" Content-Transfer-Encoding: 8bit Message-ID: <952451.30909.qm@web57105.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.768 tagged_above=0 required=5 tests=AWL=0.637, BAYES_00=-2.599, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_30_40=0.374, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200705/53 X-Sequence-Number: 25373 --0-1303462705-1178577001=:30909 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit My default_statistics_target in the postgresql.conf file is set at 250. The tables are vacuumed everyday. Now, does that mean, for each column or only for the column on which index exist, increase the value of "statistics" value, so while analyzing it uses more rows in the sample. Thanks Abu Tom Lane wrote: Abu Mushayeed writes: > I am trying to determine if I can make the planner see that nested loop is very expensive while it is trying to do an plan for a query and not pick that route. If it's doing that when it shouldn't, it's probably because it's underestimated the number of rows in the outer relation. Look to your stats settings. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend --------------------------------- Ahhh...imagining that irresistible "new car" smell? Check outnew cars at Yahoo! Autos. --0-1303462705-1178577001=:30909 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
My default_statistics_target in the postgresql.conf file is set at 250. The tables are vacuumed everyday. Now, does that mean, for each column or only for the column on which index exist, increase the value of "statistics" value, so while analyzing it uses more rows in the sample.
Thanks
Abu
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Abu Mushayeed writes:
> I am trying to determine if I can make the planner see that nested loop is very expensive while it is trying to do an plan for a query and not pick that route.

If it's doing that when it shouldn't, it's probably because it's
underestimated the number of rows in the outer relation. Look to your
stats settings.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos. --0-1303462705-1178577001=:30909--