On Tue, Aug 27, 2024 at 2:24 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > I was just using that to illustrate that making the enable_XXX GUCs > relation-local covers only a small part of the planner-control problem. > You had not, at that point, been very clear that you intended that > patch as only a small part of a solution.
Ah, OK, apologies for the lack of clarity. I actually think it's a medium part of the solution. I believe the minimum viable product here is probably something like: - control over scan methods - control over index selection - control over join methods - control over join order It gets a lot better if we also have: - control over aggregation methods - something that I'm not quite sure about for appendrels - control over whether parallelism is used and the degree of parallelism If control over index selection is already adequate, then the proposed patch is one way to get about 1/3 of the way to the MVP, which isn't nothing. Maybe I'm underestimating the amount of stuff that people are going to want here, but if you look at pg_hint_plan, it isn't doing a whole lot more than this. > I do think that index selection is pretty well under control already, > thanks to stuff that we put in ages ago at the urging of people who > wanted to write "index advisor" extensions. (The fact that that > area seems a bit moribund is disheartening, though. Is it a lack > of documentation?) So a couple of things about this. First, EDB maintains closed-source index advisor code that uses this machinery. In fact, if I'm not mistaken, we now have two extensions that use it. So it's not dead from that point of view, but of course anything closed-source can't be promoted through community channels. There's open-source code around too; to my knowledge, https://github.com/HypoPG/hypopg is the leading open-source implementation, but my knowledge may very well be incomplete. Second, I do think that the lack of documentation poses somewhat of a challenge, and our exchange about whether an IndexOptInfo needs a disabled flag is perhaps an example of that. To be fair, now that I look at it, the comment where get_relation_info_hook does say that you can remove indexes from the index list, so maybe I should have realized that the problem can be solved that way, but on the other hand, the comment for set_rel_pathlist_hook claims you can delete paths from the pathlist, which AFAICS is completely non-viable, so one can't necessarily rely too much on the comments in this area to learn what actually does and does not work. Having some in-core examples showing how to use this stuff correctly and demonstrating its full power would also be really helpful. Right now, I often find myself looking at out-of-core code which is sometimes poorly written and frequently resorts to nasty hacks. It can be hard to determine whether those nasty hacks are there because they're the only way to implement some bit of functionality or because the author missed an opportunity to do better. Third, I think there's simply a lack of critical mass in terms of our planner hooks. While the ability to add hypothetical indexes has some use, the ability to remove indexes from consideration is probably significantly more useful. But not if it's the only technique for fixing a bad plan that you have available. Nobody gets excited about a toolbox that contains just one tool. That's why I'm keen to expand what can be done cleanly via hooks, and I think if we do that and also provide either some very good documentation or some well-written example implementations, we'll get more traction here. -- Robert Haas EDB: http://www.enterprisedb.com
