Greg Stark <gsst...@mit.edu> writes: > On Sat, Apr 16, 2011 at 8:21 AM, Mark Kirkwood > <mark.kirkw...@catalyst.net.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs