Subject: Re: allowing extensions to control planner behavior

From: Tom Lane <[email-masked]>
Date: Tue, 27 Aug 2024 14:24:50 -0400

Robert Haas <[email-masked]> writes:
> Well, now I'm confused. Just yesterday, in response to the 0001 patch
> that allows extensions to exert control over the join strategy, you
> complained that "Or, if your problem is that the planner wants to scan
> index A but you want it to scan index B, enable_indexscan won't help."

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.

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?)

			regards, tom lane

---

From: Robert Haas <[email-masked]>
Date: Tue, 27 Aug 2024 15:11:15 -0400

> 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.

> area seems a bit moribund is disheartening, though.  Is it a lack

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

---

From: Tom Lane <[email-masked]>
Date: Tue, 27 Aug 2024 16:15:35 -0400

Robert Haas <[email-masked]> writes:
> I believe the minimum viable product here

> - control over join order

Seems reasonable.  It might be possible to say that our answer
to "control over join order" is to provide a hook that can modify
the "joinlist" before it's passed to make_one_rel.  If you want
to force a particular join order you can rearrange that
list-of-lists-of-range-table-indexes to do so.  The thing this
would not give you is control over which rel is picked as outer
in any given join step.  Not sure how critical that bit is.

			regards, tom lane

---

From: Jeff Davis <[email-masked]>
Date: Wed, 28 Aug 2024 12:23:52 -0700

> - control over join order

I suggest we split join order into "commutative" and "associative".

The former is both useful and seems relatively easy -- A JOIN B or B
JOIN A (though there's some nuance about when you try to make that
decision).

The latter requires controlling an explosion of possibilities, and
would be an entirely different kind of hook.

Regards,
	Jeff Davis

---

From: Robert Haas <[email-masked]>
Date: Wed, 28 Aug 2024 16:12:47 -0400

> would be an entirely different kind of hook.

My proposal in http://postgr.es/m/CA+TgmoZQyVxnRU--4g2bJonJ8RyJqNi2CHpy-=[email-masked]
seems like it can cover both cases.