On Wed, Dec 30, 2015 at 08:16:28PM +1300, David Rowley wrote: > On  I suggested an idea to make improvements to the planner around the > Equivalence Class code. Later in  Tom raised concerns with this adding > too many planning cycles for a perhaps not common enough situation. I > don't want to discuss that particular patch here, I want to discuss more > generally about the dilemma about adding more smarts to the planner to > allow it to generate a more optimal plan in order to save on execution time.
It's an important topic. > A number of ideas were suggested on the other thread about how we might go > about solving this problem. In  Simon talked about perhaps enabling > extra optimisations when the planner sees that the plan will cost more than > some given threshold. That's perhaps an option, but may not work well for > optimisations which must take place very early in planning, for example . Yeah. A slew of steps precede us assembling a notion of total cost. I bet most controversial proposed steps will happen in that early period. We'd need a rough cost earlier than we get it today, and I don't know what achieving that would look like. Simon, did you have specifics in view? > Another idea which came up was from Evgeniy , which was more of a > request not to do it this way, but never-the-less, the idea was basically > to add lots of GUCs to enable/disable each extra planner feature. This and subsequent ideas each envision some kind of optimization step blacklist. Suppose it's a bitmap with one bit per optional optimizer step. Each idea chooses blacklist membership differently, but the planner acts on the blacklist about the same way. I paraphrase the ideas in those terms below, and I offer a couple more. For this idea, the blacklist is simple: 1. User defines the blacklist fully. It's essentially handing the hard part back to the user. While I sympathize with allergic reactions to this, I like it far better than rejecting optimizations based on thinking the average case wants them disabled. work_mem likewise isn't the ideal knob for any user, but it has a simple implementation and beats "1MB per hash table is okay for everyone." > Another option which I've thought about previously was a planner_strength > GUC, at which various additional optimisations are enabled at various > predefined strength levels, so that databases which tend to spend a great > deal more execution time compared to planning time can turn this up a bit > to see if that helps change that ratio a bit. This idea is far from 2. User selects from predefined blacklists like "maximum", "default", etc. > perfect though, as who's to say that planner feature X should "kick in" > before planner feature Y? I've also often thought that it might be nice to Yep. It will be essentially impossible to build an argument to move a planner feature from one strength to another. If the feature's committer has personally experienced the problem in the field, the optimization will end up active at default planner strength. > have it so the planner does not modify the Parse object, so that the > planner has the option to throw away what it's done so far and start > planning all over again with the "planner_strength" knob turned up to the > maximum, if the cost happened to indicate that the query was going to take > a long time to execute. 3. System first plans with a specific predefined blacklist that omits speculative (low probability, high payoff) steps. If that yields a high-cost plan, it repeats planning with an empty blacklist. I agree that the planner's modification of the Query tree is a substantial roadblock. The design needs to work for one-shot plans, and we're unlikely to recoup the cost of copying every one-shot Query tree. > So here I'd very much like to kick off discussion on an acceptable way to > solve this problem, in a realistic way which we're all happy with. It's bad to add 10us per plan times 3000/s/client, but the same waste once per minute per client is no cause for concern. I advise accepting speculative planner optimizations, enabled by default when similar in cost to comparable existing steps. At the same time, I encourage developing automation to cap the waste when a client elicits millions of planner runs that don't benefit from a certain optimization. Specific lines of inquiry: 4. Derive a conservative blacklist for each rewritten Query when first planning it, and use that blacklist for subsequent plans. Some prepared statements use a custom plan every time. (Constraint exclusion is one driver of such cases.) Many facets of a Query's planning problem don't depend on parameter values, so a particular optimization will apply to all the custom plans or to none of them. Let the first plan build a blacklist of provably-irrelevant optimizations, which the plan cache stores and furnishes to later runs. The first plan after an invalidation recomputes the blacklist. 5. Add a facility that profiles a workload to generate blacklist data. Think along the lines of gcc -fprofile-generate/-fprofile-use. Start a profile; run your application load test; the server collects data sufficient to match each query with its optimal blacklist. Issue "ALTER USER appuser SET optimizer_profile = 'profname'" to adopt profile-driven blacklisting. This is the automated equivalent of (1). I recommend starting with (1), full user control over the blacklist via GUC. Specifically, I recommend one GUC expressing the list rather than one Boolean GUC per optional step. The core work of enumerating optional steps and making the planner obey a blacklist applies toward all five ideas. A GUC to set that blacklist is cheap, and it would be worth having as an escape hatch even if we add sophistication later. (2) is a dead end for the reason you gave. (3) has potential, but each of (3), (4) and (5) is a big project with great uncertainty. They shouldn't block introducing optimizer steps. Thanks, nm -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers