Re: allowing extensions to control planner behavior - Mailing list pgsql-hackers

On Tue, Aug 27, 2024 at 2:24 PM Tom Lane <tgl@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