Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id CBEC46339C8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 11 Mar 2009 00:23:20 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 40194-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 11 Mar 2009 00:23:16 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f170.google.com (mail-bw0-f170.google.com
	[209.85.218.170])
	by mail.postgresql.org (Postfix) with ESMTP id CCC2363223F
	for <pgsql-performance@postgresql.org>;
	Wed, 11 Mar 2009 00:23:12 -0300 (ADT)
Received: by bwz18 with SMTP id 18so2619992bwz.19
	for <pgsql-performance@postgresql.org>;
	Tue, 10 Mar 2009 20:23:11 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=googlemail.com; s=gamma;
	h=domainkey-signature:mime-version:sender:reply-to:received
	:in-reply-to:references:date:x-google-sender-auth:message-id:subject
	:from:to:content-type;
	bh=od9IBnqtclW+EVCLJ5mYZNBmX/MJItaLfoPeDKY+bMc=;
	b=ES4SQDwVtwMYFl78xZRORmhP6Udu6h6G1uBnVSdamupBaDTPNsjwhepQOzY4dkwHQy
	kibgBkLk0gaJUG9gFOC7siM93JO63WdgwCLkZeCL2r14XLJybaOqho2XcXusM6j9YDst
	6MtSact8o+iMgYYw0wgIA7bjD+YCED02Xs+Pg=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=mime-version:sender:reply-to:in-reply-to:references:date
	:x-google-sender-auth:message-id:subject:from:to:content-type;
	b=D52fbZCsWjorUbPBX/I5e5a6uj6ETJQQGAkqfX5g3s4CEYTOmR1UQ7Ge1JyiqFWJAr
	ja3Yf1O9IkVWtbqAf29+lAEeTiKEe/YNNncoPIVTewpvGLm4oW44ZR4YdIQr12hWq2aN
	H5RP33A0o+S4K8awmfUpigXIR/kM28WgA5xpw=
MIME-Version: 1.0
Reply-To: smclellan@mintel.com
Received: by 10.223.124.75 with SMTP id t11mr5346522far.58.1236741313400;
 Tue,
	10 Mar 2009 20:15:13 -0700 (PDT)
In-Reply-To: 
 <OFBF6E3E32.2E70AF48-ON86257576.0011218D-86257576.001121A4@MintelChicago.usdmm.com>
References: 
 <OFBF6E3E32.2E70AF48-ON86257576.0011218D-86257576.001121A4@MintelChicago.usdmm.com>
Date: Tue, 10 Mar 2009 22:15:13 -0500
X-Google-Sender-Auth: f57984f3b666bc20
Message-ID: <cfca83d70903102015m2c217852l2137ed78dd04f3e8@mail.gmail.com>
Subject: Re: Query performance over a large proportion of data
From: Steve McLellan <smclellan@mintel.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001636c5a270aa0ed60464cf46c1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200903/91
X-Sequence-Number: 33013

--001636c5a270aa0ed60464cf46c1
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

>
>
> *Scott Marlowe <scott.marlowe@gmail.com>*
> 03/10/2009 05:19 PM
>
> >
> >  Nested Loop  (cost=466.34..192962.24 rows=15329 width=12) (actual
> > time=13653.238..31332.113 rows=131466 loops=1)
>
>
> Both your query plans end with this nested loop join which is taking
> up about half your time in your query.  Notice the estimation of the
> result set is off by a factor of about 10  here, which means a nested
> loop might be not so good a choice for this.  Try increasing default
> stats target and re-analyzing to see if that helps.  1000 is the max
> you can give that a shot right off to see if it helps.  If it does,
> drop it until the numbers start to go off again and stop.
>
> For a quicker test, you can set enable_nestloop = off in the psql
> command line and then run the query by hand and see if that helps.
> Thanks - the nested loop is indeed causing problems - reducing
> seq_page_cost had the same effect of removing the nested loop for this
> query. We'd noticed the poor row count estimation. Increasing the statistics
> doesn't seem to have much effect, but we'll have more of a go with it.

--001636c5a270aa0ed60464cf46c1
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"borde=
r-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-le=
ft: 1ex;"><p><br><font size=3D"2"><b>Scott Marlowe &lt;<a href=3D"mailto:sc=
ott.marlowe@gmail.com" target=3D"_blank">scott.marlowe@gmail.com</a>&gt;</b=
></font><br>
<font size=3D"2">03/10/2009 05:19 PM</font><br><font face=3D"Monospace,Cour=
ier"><br>&gt;<br>&gt; =A0Nested Loop =A0(cost=3D466.34..192962.24 rows=3D15=
329 width=3D12) (actual<br>&gt; time=3D13653.238..31332.113 rows=3D131466 l=
oops=3D1)<br></font><br>
<br><font face=3D"Monospace,Courier">Both your query plans end with this ne=
sted loop join which is taking<br>up about half your time in your query. =
=A0Notice the estimation of the<br>result set is off by a factor of about 1=
0 =A0here, which means a nested<br>
loop might be not so good a choice for this. =A0Try increasing default<br>s=
tats target and re-analyzing to see if that helps. =A01000 is the max<br>yo=
u can give that a shot right off to see if it helps. =A0If it does,<br>drop=
 it until the numbers start to go off again and stop.<br>
</font><br><font face=3D"Monospace,Courier">For a quicker test, you can set=
 enable_nestloop =3D off in the psql<br>command line and then run the query=
 by hand and see if that helps.</font></p>Thanks - the nested loop is indee=
d causing problems - reducing seq_page_cost had
the same effect of removing the nested loop for this query. We&#39;d
noticed the poor row count estimation. Increasing the statistics
doesn&#39;t seem to have much effect, but we&#39;ll have more of a go with =
it.</blockquote></div><br>

--001636c5a270aa0ed60464cf46c1--