Re: Massive memory use for star query

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