pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRamifications of turning off Nested Loops for slow queries
5+ messages / 3 participants
[nested] [flat]
* Ramifications of turning off Nested Loops for slow queries
@ 2008-03-04 14:42 Chris Kratz <chris.kratz@vistashare.com>
2008-03-04 16:18 ` Re: Ramifications of turning off Nested Loops for slow queries Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Kratz @ 2008-03-04 14:42 UTC (permalink / raw)
To: pgsql-performance
Hello Everyone,
I had posted an issue previously that we've been unable to resolve.
An early mis-estimation in one or more subqueries causes the remainder
of the query to choose nested loops instead of a more efficient method
and runs very slowly (CPU Bound). I don't think there is any way to
"suggest" to the planner it not do what it's doing, so we are starting
to think about turning off nested loops entirely.
Here is the history so far:
http://archives.postgresql.org/pgsql-performance/2008-02/msg00205.php
At the suggestion of the list, we upgraded to 8.2.6 and are still
experiencing the same problem. I'm now installing 8.3 on my
workstation to see if it chooses a better plan, but it will take some
time to get it compiled, a db loaded, etc.
We have a number of very long running reports that will run in seconds
if nested loops are turned off. The other alternative we are
exploring is programmatically turning off nested loops just for the
problematic reports. But with the speedups we are seeing, others are
getting gun shy about having them on at all.
So, I've now been asked to ping the list as to whether turning off
nested loops system wide is a bad idea, and why or why not.
Any other thoughts or suggestions?
Thanks,
-Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ramifications of turning off Nested Loops for slow queries
2008-03-04 14:42 Ramifications of turning off Nested Loops for slow queries Chris Kratz <chris.kratz@vistashare.com>
@ 2008-03-04 16:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-03-04 17:19 ` Re: Ramifications of turning off Nested Loops for slow queries Tom Lane <tgl@sss.pgh.pa.us>
2008-03-04 18:13 ` Re: Ramifications of turning off Nested Loops for slow queries Chris Kratz <chris.kratz@vistashare.com>
0 siblings, 2 replies; 5+ messages in thread
From: Kevin Grittner @ 2008-03-04 16:18 UTC (permalink / raw)
To: pgsql-performance; Chris Kratz <chris.kratz@vistashare.com>
>>> On Tue, Mar 4, 2008 at 8:42 AM, in message
<483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>, Chris Kratz
<chris.kratz@vistashare.com> wrote:
> So, I've now been asked to ping the list as to whether turning off
> nested loops system wide is a bad idea, and why or why not.
In our environment, the fastest plan for a lot of queries involve
nested loops. Of course, it's possible that these never provide the
fasted plan in your environment, but it seems very unlikely --
you're just not noticing the queries where it's doing fine.
> Any other thoughts or suggestions?
Make sure your effective_cache_size is properly configured.
Increase random_page_cost and/or decrease seq_page_cost.
You can play with the cost settings on a connection, using EXPLAIN
on the query, to see what plan you get with each configuration
before putting it into the postgresql.conf file.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ramifications of turning off Nested Loops for slow queries
2008-03-04 14:42 Ramifications of turning off Nested Loops for slow queries Chris Kratz <chris.kratz@vistashare.com>
2008-03-04 16:18 ` Re: Ramifications of turning off Nested Loops for slow queries Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-03-04 17:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-04 18:16 ` Re: Ramifications of turning off Nested Loops for slow queries Chris Kratz <chris.kratz@vistashare.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-03-04 17:19 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance@postgresql.org, "Chris Kratz" <chris.kratz@vistashare.com>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> On Tue, Mar 4, 2008 at 8:42 AM, in message
> <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>, Chris Kratz
> <chris.kratz@vistashare.com> wrote:
>> So, I've now been asked to ping the list as to whether turning off
>> nested loops system wide is a bad idea, and why or why not.
> In our environment, the fastest plan for a lot of queries involve
> nested loops. Of course, it's possible that these never provide the
> fasted plan in your environment, but it seems very unlikely --
> you're just not noticing the queries where it's doing fine.
Yeah, I seem to recall similar queries from other people who were
considering the opposite, ie disabling the other join types :-(
The rule of thumb is that nestloop with an inner indexscan will beat
anything else for pulling a few rows out of a large table. But on
the other hand it loses big for selecting lots of rows. I don't think
that a global disable in either direction would be a smart move, unless
you run only a very small number of query types and have checked them
all.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ramifications of turning off Nested Loops for slow queries
2008-03-04 14:42 Ramifications of turning off Nested Loops for slow queries Chris Kratz <chris.kratz@vistashare.com>
2008-03-04 16:18 ` Re: Ramifications of turning off Nested Loops for slow queries Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-03-04 17:19 ` Re: Ramifications of turning off Nested Loops for slow queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-04 18:16 ` Chris Kratz <chris.kratz@vistashare.com>
0 siblings, 0 replies; 5+ messages in thread
From: Chris Kratz @ 2008-03-04 18:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-performance
On 3/4/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > On Tue, Mar 4, 2008 at 8:42 AM, in message
> > <483ACAF5-A485-40D9-9D7E-7008EF12F909@vistashare.com>, Chris Kratz
> > <chris.kratz@vistashare.com> wrote:
> >> So, I've now been asked to ping the list as to whether turning off
> >> nested loops system wide is a bad idea, and why or why not.
>
> > In our environment, the fastest plan for a lot of queries involve
> > nested loops. Of course, it's possible that these never provide the
> > fasted plan in your environment, but it seems very unlikely --
> > you're just not noticing the queries where it's doing fine.
>
>
> Yeah, I seem to recall similar queries from other people who were
> considering the opposite, ie disabling the other join types :-(
>
> The rule of thumb is that nestloop with an inner indexscan will beat
> anything else for pulling a few rows out of a large table. But on
> the other hand it loses big for selecting lots of rows. I don't think
> that a global disable in either direction would be a smart move, unless
> you run only a very small number of query types and have checked them
> all.
>
> regards, tom lane
>
So, if we can't find another way to solve the problem, probably our best bet
is to turn off nested loops on particularly bad queries by prepending them
w/ set enable_nested_loop=off? But, leave them on for the remainder of the
system?
Do you think it's worth testing on 8.3 to see if the estimator is able to
make a better estimate?
-Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Ramifications of turning off Nested Loops for slow queries
2008-03-04 14:42 Ramifications of turning off Nested Loops for slow queries Chris Kratz <chris.kratz@vistashare.com>
2008-03-04 16:18 ` Re: Ramifications of turning off Nested Loops for slow queries Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-03-04 18:13 ` Chris Kratz <chris.kratz@vistashare.com>
1 sibling, 0 replies; 5+ messages in thread
From: Chris Kratz @ 2008-03-04 18:13 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
On 3/4/08, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>
> >>> On Tue, Mar 4, 2008 at 8:42 AM, in message
> > Any other thoughts or suggestions?
>
>
> Make sure your effective_cache_size is properly configured.
>
> Increase random_page_cost and/or decrease seq_page_cost.
> You can play with the cost settings on a connection, using EXPLAIN
> on the query, to see what plan you get with each configuration
> before putting it into the postgresql.conf file.
>
>
> -Kevin
That was a good idea. I hadn't tried playing with those settings in a
session. This is a 8G box, and we've dedicated half of that (4G) to the
file system cache. So, 4G is what effective_cache_size is set to. Our
seq_page_cost is set to 1 and our random_page_cost is set to 1.75 in the
postgresql.conf.
In testing this one particular slow query in a session, I changed these
settings alternating in increments of 0.25. The random_page_cost up to 4
and the seq_page_cost down to 0.25. This made perhaps a second difference,
but at the end, we were back to to the 37s. Doing a set enable_nestloop=off
in the session reduced the runtime to 1.2s with the other settings back to
our normal day to day settings.
So, for now I think we are going to have to modify the code to prepend the
problematic queries with this setting and hope the estimator is able to
better estimate this particular query in 8.3.
Thanks for the suggestions,
-Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-03-04 18:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-04 14:42 Ramifications of turning off Nested Loops for slow queries Chris Kratz <chris.kratz@vistashare.com>
2008-03-04 16:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-03-04 17:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-04 18:16 ` Chris Kratz <chris.kratz@vistashare.com>
2008-03-04 18:13 ` Chris Kratz <chris.kratz@vistashare.com>
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