From: Samuel Gendler <sgendler@ideasculptor.com>
To: pgsql-performance@postgresql.org
Subject: Re: near identical queries have vastly different plans
Date: Thu, 30 Jun 2011 02:40:10 -0700
Message-ID: <BANLkTimz9GDfyn7-c6UmRjZ9RHaDranjrw@mail.gmail.com> (raw)
In-Reply-To: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com>
References: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgendler@ideasculptor.com
Subject: Re: near identical queries have vastly different plans
In-Reply-To: <BANLkTimz9GDfyn7-c6UmRjZ9RHaDranjrw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox