Re: Massive memory use for star query

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Sat, Apr 16, 2011 at 8:21 AM, Mark Kirkwood
> <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:
>> I guess you have answered my first question - i.e yes this should eat
>> massive amount of ram as written - however are you sure there is no memory
>> leaking going on here?

> 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.

regards, tom lane