Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id D03C4B5DBE1
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 30 Jun 2011 06:40:17 -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 94219-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 30 Jun 2011 09:40:11 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com
	[74.125.83.174])
	by mail.postgresql.org (Postfix) with ESMTP id 60E47B5DBF6
	for <pgsql-performance@postgresql.org>;
	Thu, 30 Jun 2011 06:40:11 -0300 (ADT)
Received: by pvg12 with SMTP id 12so1454540pvg.19
	for <pgsql-performance@postgresql.org>;
	Thu, 30 Jun 2011 02:40:10 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.68.13.228 with SMTP id k4mr2340417pbc.40.1309426810601; Thu,
	30 Jun 2011 02:40:10 -0700 (PDT)
Received: by 10.68.62.164 with HTTP; Thu, 30 Jun 2011 02:40:10 -0700 (PDT)
X-Originating-IP: [66.215.95.51]
In-Reply-To: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com>
References: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com>
Date: Thu, 30 Jun 2011 02:40:10 -0700
Message-ID: <BANLkTimz9GDfyn7-c6UmRjZ9RHaDranjrw@mail.gmail.com>
Subject: Re: near identical queries have vastly different plans
From: Samuel Gendler <sgendler@ideasculptor.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=bcaec5216315e7beed04a6eaaf82
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201106/276
X-Sequence-Number: 44209

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

On Thu, Jun 30, 2011 at 1:53 AM, Samuel Gendler
<sgendler@ideasculptor.com>wrote:

> If I could figure out either a query structure or an index structure which
> will force the fast query plan, I'd be much happier.  So that is what I am
> looking for - an explanation of how I might convince the planner to always
> use the fast plan.
>
>
For the record, "set enable_nestloop=false" does force a more effective plan
when using the 'slow' query.  It is not quite identical in structure - it
materializes the other side of the query, resulting in about 10% less
performance - but it is close enough that I'm tempted to disable nestloop
whenever I run the query in the hope that it will prevent the planner from
switching to the really awful plan.  I know that's kind of a drastic
measure, so hopefully someone out there will suggest a config fix which
accomplishes the same thing without requiring special handling for this
query, but at least it works (for now).

Incidentally, upgrading to 9.0.x is not out of the question if it is
believed that doing so might help here.  I'm only running 8.4 because I've
got another project in production on 8.4 and I don't want to have to deal
with running both versions on my development laptop.  But that's a pretty
weak reason for not upgrading, I know.

--sam

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

<br><br><div class=3D"gmail_quote">On Thu, Jun 30, 2011 at 1:53 AM, Samuel =
Gendler <span dir=3D"ltr">&lt;<a href=3D"mailto:sgendler@ideasculptor.com">=
sgendler@ideasculptor.com</a>&gt;</span> wrote:<br><blockquote class=3D"gma=
il_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-lef=
t:1ex;">
<div>If I could figure out either a query structure or an index structure w=
hich will force the fast query plan, I&#39;d be much happier. =A0So that is=
 what I am looking for - an explanation of how I might convince the planner=
 to always use the fast plan.</div>

<div><br></div></blockquote><div><br></div><div>For the record, &quot;set e=
nable_nestloop=3Dfalse&quot; does force a more effective plan when using th=
e &#39;slow&#39; query. =A0It is not quite identical in structure - it mate=
rializes the other side of the query, resulting in about 10% less performan=
ce - but it is close enough that I&#39;m tempted to disable nestloop whenev=
er I run the query in the hope that it will prevent the planner from switch=
ing to the really awful plan. =A0I know that&#39;s kind of a drastic measur=
e, so hopefully someone out there will suggest a config fix which accomplis=
hes the same thing without requiring special handling for this query, but a=
t least it works (for now).</div>
<div><br></div><div>Incidentally, upgrading to 9.0.x is not out of the ques=
tion if it is believed that doing so might help here. =A0I&#39;m only runni=
ng 8.4 because I&#39;ve got another project in production on 8.4 and I don&=
#39;t want to have to deal with running both versions on my development lap=
top. =A0But that&#39;s a pretty weak reason for not upgrading, I know.</div=
>
<div><br></div><div>--sam</div><div><br></div></div>

--bcaec5216315e7beed04a6eaaf82--