Re: allowing extensions to control planner behavior

Hi Robert,

On Mon, Aug 26, 2024 at 6:33 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> I'm somewhat expecting to be flamed to a well-done crisp for saying
> this, but I think we need better ways for extensions to control the
> behavior of PostgreSQL's query planner.
[..]
> [..] But all that
> said, as much as anything, I want to get some feedback on what
> approaches and trade-offs people think might be acceptable here,
> because there's not much point in me spending a bunch of time writing
> code that everyone (or a critical mass of people) are going to hate.

As a somewhat tiny culprit of the self-flaming done by Robert (due to
nagging him about this in the past on various occasions), I'm of
course obligated to +1 to any work related to giving end-users/DBA the
ability to cage the plans generated by the optimizer.

When dealing with issues like those, I have a feeling we have 2
classes of most frequent issues being reported (that's my subjective
experience):
a. cardinality misestimate leading usually to nest loop plans (e.g.
JOIN estimates thread [1] could also somehow help and it also has nice
reproducers)
b. issues after upgrades

So the "selectivity estimation hook(s)" mentioned by Andrei seems to
be a must, but also the ability not to just guess & tweak (shape) the
plan, but a way to export all SQL plans before upgrade with capability
to import and override(lock) specific SQL query to specific plan from
before upgrade.

I'm not into the internals of optimizer at all, but here are other
random thoughts/questions:
- I do think that "hints" words have bad connotations and should not
be used. It might be because of embedding them in SQL query text of
the application itself. On one front they are localized to the SQL
(good), but the PG operator has no realistic way of altering that once
it's embedded in binary (bad), as the application team is usually
separate if not from an external company (very bad situation, but
happens almost always).
- Would stacking of such extensions, each overriding the same planner
hooks, be allowed or not in the long run ? Technically there's nothing
preventing it and I think I could imagine someone attempting to run
multiple planner hook hotfixes for multiple issues, all at once?
- Shouldn't EXPLAIN contain additional information that for that
specific plan the optimizer hooks changed at least 1 thing ? (e.g.
"Plan was tainted" or something like that). Maybe extension could mark
it politely that it did by setting a certain flag, or maybe there
should be routines exposed by the core to do that ?
- the "b" (upgrade) seems like a much more heavy duty issue, as that
would require transfer of version-independent and textualized dump of
SQL plan that could be back-filled into a newer version of optimizer.
Is such a big thing realistic at all and it's better to just
concentrate on the hooks approach?

-J.

[1] - https://www.postgresql.org/message-id/flat/c8c0ff31-3a8a-7562-bbd3-78b2ec65f16c%40enterprisedb.com