On Tue, Mar 3, 2026 at 2:28 PM David G. Johnston <[email protected]> wrote: > I mostly get why specifying an index that doesn't exist as part of advice, > alongside a target relation, produces "matched" along with "inapplicable" and > "failed". > > INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */ > > But less understandable is why a failure to match a subplan-qualified target > produces "matched" when the subplan doesn't appear. > > SEQ_SCAN(t1@minmax_1) /* matched, failed */
Because there's not a way to control aggregation behavior at present, you can't directly conrol whether t1 or t1@minmax_1 appears in the winning plan. This case is "matched" because we saw t1@minmax_1 get planned, but it's "failed" because the non-minmax strategy then won on cost. Aside: This is actually an instance of a very common problem into which I have already invested an enormous amount of time and energy. Suppose that for some operation X (which might be a scan or a join or any other kind of thing) you tell the planner that you want it to use strategy S. If strategy S is not what was going to be chosen anyway, you've just increased the cost of X. Unsurprisingly, the planner's response to that is very often to choose not to perform X at all. Otherwise, it ends up looking like the planner has just made an end-run around the advice. For example, HASH_JOIN(X) says "put X on the inner side of a hash join". Obviously, this means that when we see a join with X as the inner rel, we should disallow all join strategies other than hash join. Less obviously, it also means that when we see a join with X as the outer rel, we shouldn't allow *any* join strategies. If you don't do that, you get a problem very similar to what you're complaining about here. The difference is that join control is supported by the patch set, and so I have put in the work to avoid instances of this problem that occur in that case, and aggregate control is not, so I haven't. > Maybe we need to do something like: > > relname - matches anywhere in the plan tree > relname@somewhere - only looks at "somewhere" for matches; absence of > somewhere results in "not matched" (the expected feedback for the > advice/query combination above). This would not work in general; "anywhere in the plan tree" is too broad a scope, and it would be easy to construct an example where it falsely matches an unrelated part of the query. I think that the solution here probably looks more like letting the user write AGGREGATE_PLAIN(something) or AGGREGATE_MINMAX(something), but to make it really work, we would need to figure out what the "something" should be, and also remember to account for partitionwise aggregation, eager aggregation, partial aggregation, hashed aggregation, sorted aggregation, plain aggregation, and mixed aggregation, some of which can be combined with some of the others but not all of the others, and then we would need to add design and implement hooks in core to allow that advice to be enforced, and then we would need to get pg_plan_advice to properly accept the syntax and enforce it and also be able to generate the syntax from a finished plan tree, and then document all that and add tests. I think that work is worth doing, but, again, I think it would be a much better idea to spend the next few weeks trying to figure out whether it makes sense to commit what I've got, and perform any necessarily stabilization of that functionality, than to keep saying "hey, maybe you should radically expand the scope." It's probably a six-month project to get all that working, and we don't have that time for v19. > If keeping the status quo the existing behavior should be documented. The > existing wording for not matched; "or it may occur if the relevant portion of > the query was not planned," seems to be the one that covers this case. That's definitely relevant here, although these specific examples seem to have more to do with "failed" than "not matched". -- Robert Haas EDB: http://www.enterprisedb.com
