Re: On disable_cost - Mailing list pgsql-hackers

I'd encourage anyone else on the sidelines who has an opinion on how
to display the disabled-ness of a plan node in EXPLAIN to speak up
now, even if it's just a +1 to something someone has already written.
It would be nice to see what more people think.

1, that the plan shown to me is constrained,
2, which constraints are in place, and
3, which constraints were violated.

The Settings option to Explain fulfills my second need.  It is not a precise solution nor is it automatic.  Because of these two things it really doesn't qualify as fulfilling the first need.

To fulfill the first need I would want to see a data block containing the following information:
How many (>= 1) enable_* settings are set to false.  This is the bare requirement, but we can also include a count of how many violations exist, thus aggregating the count of the third need. This information is not specific to any node and thus should be displayed outside of the execution tree, the specific choice consistent with the output format under consideration.

The detail for the third need, violations, is tied to specific executor nodes.  The information provided here should inform me as to which specific setting was violated as well as, if possible, why. This is effectively three pieces of information:  "Disabled: * (footnote)"  The word disabled is the indicator that this node type was requested to not be included in the query plan.  The * tells me exactly which of the disabled settings is at play here, reducing the cognitive burden of memorizing node types to settings.  The footnote would be a reference into the documentation under the enable_* setting that explains why this node is appearing in the query plan even though I explicitly asked for it to be excluded.  In a verbose output (add a new violations option for this) it would even be possible to save the trip to the documentation by adding the footnote text to the explain output.

Now, existing proposals include another piece of data - for every node calculate how many violations occur in its tree (inclusive).  I'm not understanding the motivation for this data.  Knowing which nodes are violations seems like it is enough.  I could always count, and processing tools could add this aggregate to their displays, but the count itself only seems useful at the scope of the entire query plan.  And possibly sub-plans.

So, by way of example:

set enable_sort=0;
explain (costs off, settings, violations) select * from lp order by a;

 Append
   ->  Index Only Scan using lp1_a_idx on lp1 lp_1
   ->  Sort
         Disabled: Sort (A)
         Sort Key: lp_2.a
         ->  Seq Scan on lp2 lp_2
         
Disabled Planner Settings: 1
Disabled Node Violations: 1
Settings:
     ...
    enable_sort = false
....
Violation Reasons:
Sort (A): The query contains an order by clause over data coming from a table being sequentially scanned.  This scan's output must be sorted to fulfill the order by requirement.

I was considering doing a layout like:

Sort (disabled_sort.A) (cost...) (actual...)

but having its own line on those nodes having the violation seems reasonable.  It should be noticeable when the violations occur and this does stand out.  The less pronounced format would be more appropriate for the "Disabled: #" display that would appear on every single node; which I believe is counter-productive.  Only marking the violation provides the same amount of detail and allows for the computation of those counts should the need arise.  As a DBA, though, I do not know how to use that count in a meaningful way.

In text format we place additional information at the bottom of the query result.  It is worth considering whether to place information before the planner tree.  If that is acceptable the two "Disabled Planner %:" counts should be moved to before the node tree.  This draws immediate attention to the explain output consumer that this plan is constrained and that other options, like settings and violations, should be added to the explain command to show additional details.  But the two counts and the node detail "Disabled: * (footnote)" will always be visible.

The footnote definitely is its own feature added to increase usability.  I'm expecting it to not be accepted given the current design of explain, and also it seems quite difficult to get good data out of the planner to make the display accurate.  But if we tell someone that a setting they disable is violated they are going to wonder why.