Skip site navigation (1) Skip section navigation (2)

Re: Massive memory use for star query

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Massive memory use for star query
Date: 2011-04-16 23:54:31
Message-ID: 4DAA2C37.1010903@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-bugs
On 17/04/11 02:58, Tom Lane wrote:
> Greg Stark<gsstark(at)mit(dot)edu>  writes:
>> The planner uses various heuristics to avoid combinatoric growth
>> wherever it can but there's no way to completely avoid it.
> Yeah.  The collapse_limit variables can be seen as another heuristic to
> deal with this type of problem: they artificially limit the number of
> combinations considered by forcing the join search to be broken down
> into subproblems.  The trouble of course is that this breakdown is
> pretty stupid and can easily prevent the best join order from ever being
> considered.
>
> If you've got a small number of such query types that you can afford to
> spend some manual effort on, here's what I'd do:
>
> 1. With those three planner variables cranked up to more than the number
> of relations in the query (if possible), run an EXPLAIN, or better
> EXPLAIN ANALYZE so you can confirm you get a good plan.
>
> 2. Observe the join order selected in the good plan.
>
> 3. Rearrange your query so that the tables are explicitly JOINed in that
> order.  Don't use the FROM-comma-list style.
>
> 4. Now, in your production app, *reduce* join_collapse_limit to a small
> value, maybe even 1, to force the syntactic JOIN order to be followed.
> (Obviously, don't keep it there when running queries you haven't
> hand-optimized this way.)
>
> This will force the planner to consider only small subproblems, which
> will make it both much faster and much less memory-hungry than when it's
> trying to solve a large join problem from scratch.
>

We've sort of done an equivalent thing as a temporary fix - restricted 
the page generating these queries to one or two keywords to tame the 
number of tables joined in.

We are only seeing this type of query being generated in a very specific 
part of the application (keyword search), and I've been encouraging a 
redesign in that area anyway as I don't believe it is necessary to 
require so many joins to achieve what they wish to do - so this is 
really the clincher for a redesign.

I will get 'em to reduce the *collapse limits too.

Thanks to all of you for your help, regards

Mark

In response to

pgsql-bugs by date

Next:From: John R PierceDate: 2011-04-17 00:43:51
Subject: Re: BUG #5983: creating accounts
Previous:From: Mark KirkwoodDate: 2011-04-16 23:18:54
Subject: Re: Massive memory use for star query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group