Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 9C2EEB5DBE0
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 25 May 2011 12:48:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 54341-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 25 May 2011 15:47:56 +0000 (UTC)
X-Greylist: delayed 00:18:06.928784 by SQLgrey-1.7.6
Received: from mail-gy0-f174.google.com (mail-gy0-f174.google.com
	[209.85.160.174])
	by mail.postgresql.org (Postfix) with ESMTP id 52ADAB5DBDE
	for <pgsql-sql@postgresql.org>; Wed, 25 May 2011 12:47:56 -0300 (ADT)
Received: by gyd10 with SMTP id 10so3078943gyd.19
	for <pgsql-sql@postgresql.org>; Wed, 25 May 2011 08:47:55 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:in-reply-to:references:date
	:message-id:subject:from:to:cc:content-type;
	bh=hIAPpVRq1/GOfUUS2f0oj3stjlU7WCM286HdHZYHHHA=;
	b=CafxRVSrqKS1EmiH/Ih/aThhdxYKDRrL7/1fHrN09/xomEODi04LnaJhE7vs5lM8KY
	Kuq+pLvWMWjFvAwyoOW19to4LEKS6cRnZEsXxvtzS/xkOtLVkGJygeoI9xYtluLarpI/
	v7OayIaaNNRGAOCuycnetAEU0SbG33klylIRA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=ngpC5BZrgV9/u4C67tOJwo6pPiF7Rol4C/3V1Mgwf8zvpPDVTNY+Q7UFJS4pUQ3tpw
	FtIVwPkMRvrQsbP9pdPuF+0lWU/ao/aBP6x2rP9Q0rSl7Sfq3OMQrUf7wW6oQy2LoRhu
	1LuDvQpfD2iWTPrLLQ7t53SYxWvBDxVRjXLO0=
MIME-Version: 1.0
Received: by 10.236.153.231 with SMTP id f67mr6873312yhk.52.1306336993060;
	Wed, 25 May 2011 08:23:13 -0700 (PDT)
Received: by 10.236.102.175 with HTTP; Wed, 25 May 2011 08:23:12 -0700 (PDT)
In-Reply-To: <4884.1306332801@sss.pgh.pa.us>
References: <BANLkTimo9PG9r+pP-wxT=Amk79G_UmtCwg@mail.gmail.com>
	<4DD10533.7050904@postnewspapers.com.au>
	<BANLkTi=GmO5NaTGb80e7LpX8mRcvPyKucg@mail.gmail.com>
	<BANLkTinp_jkY5mpruP8JkTekpDKMkjovyQ@mail.gmail.com>
	<BANLkTim_EvVzTzzbbOhmDQGPMUoc0fNLFA@mail.gmail.com>
	<BANLkTi=X8CBCF_mx4SEp1Wh3b2Gn6vS_mg@mail.gmail.com>
	<BANLkTin1TRna9JDL9se+j7ROdaATXSqP+A@mail.gmail.com>
	<4884.1306332801@sss.pgh.pa.us>
Date: Wed, 25 May 2011 17:23:12 +0200
Message-ID: <BANLkTikRhu7NYb9FdorgMP9hrXW6p+Z-Lw@mail.gmail.com>
Subject: Re: Performance of NOT IN and <> with PG 9.0.4
From: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=20cf302d489e6da60804a41b4812
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.897 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RFC_ABUSE_POST=0.001
X-Spam-Level: 
X-Archive-Number: 201105/156
X-Sequence-Number: 35440

--20cf302d489e6da60804a41b4812
Content-Type: text/plain; charset=ISO-8859-1

As I've understood the docs those 2 limits should not take effect, because
the performance is going down when adding two aggregated columns, but only
when enable_material is on.

2011/5/25 Tom Lane <tgl@sss.pgh.pa.us>

> Robert Haas <robertmhaas@gmail.com> writes:
> > On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic
> > <jasmin.dizdarevic@gmail.com> wrote:
> >> enable_material = off
>
> > Is there any chance you can reproduce this with a simpler test case
> > that doesn't involve quite so many joins?
>
> I didn't stop to count, but are there enough that join_collapse_limit
> or from_collapse_limit could be in play?
>
>                        regards, tom lane
>

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

As I&#39;ve understood the docs those 2 limits should not take effect, beca=
use the performance is going down when adding two aggregated columns, but o=
nly when enable_material is on.<br><br><div class=3D"gmail_quote">2011/5/25=
 Tom Lane <span dir=3D"ltr">&lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@ss=
s.pgh.pa.us</a>&gt;</span><br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;"><div class=3D"im">Robert Haas &lt;<a href=
=3D"mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>&gt; writes:<br>
&gt; On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic<br>
&gt; &lt;<a href=3D"mailto:jasmin.dizdarevic@gmail.com">jasmin.dizdarevic@g=
mail.com</a>&gt; wrote:<br>
&gt;&gt; enable_material =3D off<br>
<br>
&gt; Is there any chance you can reproduce this with a simpler test case<br=
>
&gt; that doesn&#39;t involve quite so many joins?<br>
<br>
</div>I didn&#39;t stop to count, but are there enough that join_collapse_l=
imit<br>
or from_collapse_limit could be in play?<br>
<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane<br>
</blockquote></div><br>

--20cf302d489e6da60804a41b4812--