On 17/04/11 02:58, Tom Lane wrote:
Greg Stark<gsst...@mit.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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
