From: Stephen Frost <sfrost@snowman.net>
To: Marty Frasier <m.frasier@escmatrix.com>
Cc: pgsql-performance@postgresql.org, James Quinn <j.quinn@escmatrix.com>
Subject: Re: how to help the planner
Date: Thu, 28 Mar 2013 18:13:11 -0400
Message-ID: <20130328221311.GU4361@tamriel.snowman.net> (raw)
In-Reply-To: <CAAX5s64o2LCF0cWNqDMTiM1JuSyyESvh1T1QW1cmDTpi4n2rdg@mail.gmail.com>
References: <CAAX5s64o2LCF0cWNqDMTiM1JuSyyESvh1T1QW1cmDTpi4n2rdg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Marty,
* Marty Frasier (m.frasier@escmatrix.com) wrote:
> We have a particular query that takes about 75 minutes to complete. The
> selected execution plan estimates 1 row from several of the outermost
> results so picks nested loop join resolutions. That turns out to be a bad
> choice since actual row counts are in the thirty to fifty thousand range.
I've seen exactly this behaviour and it's led to many cases where we've
had to simply disable nest loop for a given query. They're usually in
functions, so that turns out to be workable without having to deal with
application changes. Still, it totally sucks.
> I haven't found where
> it's set yet but presume it was unable to determine the result set row
> count and defaulted to 1.
No.. There's no 'default to 1', afaik. The problem seems to simply be
that PG ends up estimating the number of rows coming back very poorly.
I'm actually suspicious that the number it's coming up with is much
*smaller* than one and then clamping it back to '1' as a minimum instead
of rounding it down to zero. I did see one query that moved to a nested
loop query plan from a more sensible plan when upgrading from 9.0 to
9.2, but there were plans even under 9.0 that were similairly bad.
The one thing I've not had a chance to do yet is actually build out a
test case which I can share which demonstrates this bad behaviour. If
that's something which you could provide, it would absolutely help us in
understanding and perhaps solving this issue.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 197B) download
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: sfrost@snowman.net, m.frasier@escmatrix.com, j.quinn@escmatrix.com
Subject: Re: how to help the planner
In-Reply-To: <20130328221311.GU4361@tamriel.snowman.net>
* 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