Re: allowing extensions to control planner behavior

On Wed, Oct 23, 2024 at 11:51 AM Andrei Lepikhov wrote: > Parse tree may be implemented with multiple execution plans. Even > clauses can be transformed during optimisation (Remember OR -> ANY). > Also, the cardinality of a middle-tree join depends on the inner and > outer subtrees. Because of tha

Re: allowing extensions to control planner behavior

On 10/23/24 15:05, Robert Haas wrote: On Sat, Oct 19, 2024 at 6:00 AM Andrei Lepikhov wrote: Generally, a hash value doesn't 100% guarantee the uniqueness of a node identification. Also, RelOptInfo corresponds to a subtree in the final plan, and sometimes, it takes work to find which node in th

Re: allowing extensions to control planner behavior

On Sat, Oct 19, 2024 at 6:00 AM Andrei Lepikhov wrote: > Generally, a hash value doesn't 100% guarantee the uniqueness of a node > identification. Also, RelOptInfo corresponds to a subtree in the final > plan, and sometimes, it takes work to find which node in the partially > executed plan corresp

Re: allowing extensions to control planner behavior

On 18/10/2024 14:56, Jakub Wartak wrote: Hi Andrei, On Fri, Oct 11, 2024 at 8:21 AM Andrei Lepikhov > wrote: out of curiosity, why do You think it would be inappropriate to do so in the core? Maybe it Is something similiar to compute_query_id that can be computed exte

Re: allowing extensions to control planner behavior

Hi Andrei, On Fri, Oct 11, 2024 at 8:21 AM Andrei Lepikhov wrote: > On 10/10/24 23:51, Robert Haas wrote: > > On Wed, Sep 18, 2024 at 11:48 AM Robert Haas > wrote: > > 1. If you want to specify in-query hints using comments, how does your > > extension get access to the comments? > Having desi

Re: allowing extensions to control planner behavior

On Mon, Oct 14, 2024 at 6:02 AM Jakub Wartak wrote: >> I wonder if we could think about reversing the order of operations >> here and making it so that we do the distinct-ification during parse >> analysis or maybe early in planning, so that the name you see EXPLAIN >> print out is the real name o

Re: allowing extensions to control planner behavior

Hi Robert, On Thu, Oct 10, 2024 at 6:52 PM Robert Haas wrote: > On Wed, Sep 18, 2024 at 11:48 AM Robert Haas > wrote: > > Still, I think it's a pretty useful starting point. It is mostly > > enough to give you control over join planning, and if combined with > > similar work for scan planning,

Re: allowing extensions to control planner behavior

On 10/10/24 23:51, Robert Haas wrote: On Wed, Sep 18, 2024 at 11:48 AM Robert Haas wrote: 1. If you want to specify in-query hints using comments, how does your extension get access to the comments? Having designed two features [1,2] that do the stuff mostly similar to pg_hint_plan but based on

Re: allowing extensions to control planner behavior

On Wed, Sep 18, 2024 at 11:48 AM Robert Haas wrote: > Still, I think it's a pretty useful starting point. It is mostly > enough to give you control over join planning, and if combined with > similar work for scan planning, I think it would be enough for > pg_hint_plan. If we also got control over

Re: allowing extensions to control planner behavior

On 10/4/24 01:35, Robert Haas wrote: On Mon, Sep 30, 2024 at 5:50 AM Andrei Lepikhov wrote: In attachment - hooks for add_path and add_partial_path. As you can see, because of differences in these routines hooks also implemented differently. Also the compare_path_costs_fuzzily is exported, but

Re: allowing extensions to control planner behavior

On Mon, Sep 30, 2024 at 5:50 AM Andrei Lepikhov wrote: > Being flexible, this approach is less invasive. Now, I use it to > implement heuristics demanded by clients for cases when the estimator > predicts only one row - usually, it means that the optimiser > underestimates cardinality. For example

Re: allowing extensions to control planner behavior

On 18/9/2024 17:48, Robert Haas wrote: Comments? Let me share my personal experience on path management in the planner. The main thing important for extensions is flexibility - I would discuss a decision that is not limited by join ordering but could be applied to implement an index picking st

Re: allowing extensions to control planner behavior

On Tue, Aug 27, 2024 at 4:07 PM Robert Haas wrote: > I think that the beginning of add_paths_to_joinrel() looks like a > useful spot to get control. You could, for example, have a hook there > which returns a bitmask indicating which of merge-join, nested-loop, > and hash join will be allowable fo

Re: allowing extensions to control planner behavior

On Fri, Aug 30, 2024 at 1:42 PM Jeff Davis wrote: > As far as performance goes, I'm only looking at branch in add_path() > that calls compare_pathkeys(). Do you have some example queries which > would be a worst case for that path? I think we must be talking past each other somehow. It seems to m

Re: allowing extensions to control planner behavior

On Fri, 2024-08-30 at 07:33 -0400, Robert Haas wrote: > This is a fair point. I dislike the fact that add_path() is a thicket > of if-statements that's actually quite hard to understand and easy to > screw up when you're making modifications. But I feel like it would > be > difficult to generalize

Re: allowing extensions to control planner behavior

On Thu, Aug 29, 2024 at 6:49 PM Jeff Davis wrote: > I don't see that in the code yet, so I assume you are referring to the > comment at [1]? FYI, I'm hacking on a revised approach but it's not ready to show to other people yet. > I still like my idea to generalize the pathkey infrastructure, and

Re: allowing extensions to control planner behavior

On Wed, 2024-08-28 at 19:25 -0400, Tom Lane wrote: > This does not seem remarkably problematic to me, given Robert's > proposal of a bitmask of allowed plan types per RelOptInfo. > You just do something like > > rel->allowed_plan_types = DESIRED_PLAN_TYPE; > > The names of the bits you ar

Re: allowing extensions to control planner behavior

On Wed, Aug 28, 2024 at 07:25:59PM -0400, Tom Lane wrote: > Jeff Davis writes: > > Beyond awkwardness, one case where it matters is the interaction > > between an extension that provides hints and an extension that offers a > > CustomScan. How is the hints extension supposed to disable a path it >

Re: allowing extensions to control planner behavior

Jeff Davis writes: > Beyond awkwardness, one case where it matters is the interaction > between an extension that provides hints and an extension that offers a > CustomScan. How is the hints extension supposed to disable a path it > doesn't know about? This does not seem remarkably problematic to

Re: allowing extensions to control planner behavior

On Wed, 2024-08-28 at 16:35 -0400, Robert Haas wrote: > On Wed, Aug 28, 2024 at 4:29 PM Jeff Davis wrote: > > Preserving a path for the right amount of time seems like the > > primary > > challenge for most of the use cases you raised (removing paths is > > easier than resurrecting one that was pr

Re: allowing extensions to control planner behavior

On Wed, Aug 28, 2024 at 4:29 PM Jeff Davis wrote: > Preserving a path for the right amount of time seems like the primary > challenge for most of the use cases you raised (removing paths is > easier than resurrecting one that was pruned too early). If we try to > keep a path around, that implies t

Re: allowing extensions to control planner behavior

On Mon, 2024-08-26 at 12:32 -0400, Robert Haas wrote: > I think there are two basic approaches that are possible here. If > someone sees a third option, let me know. First, we could allow users > to hook add_path() and add_partial_path(). ... > The other possible approach is to allow extensions

Re: allowing extensions to control planner behavior

On Wed, Aug 28, 2024 at 3:23 PM Jeff Davis wrote: > On Tue, 2024-08-27 at 15:11 -0400, Robert Haas wrote: > > - control over scan methods > > - control over index selection > > - control over join methods > > - control over join order > > I suggest we split join order into "commutative" and "assoc

Re: allowing extensions to control planner behavior

On Tue, 2024-08-27 at 15:11 -0400, Robert Haas wrote: > - control over scan methods > - control over index selection > - control over join methods > - control over join order I suggest we split join order into "commutative" and "associative". The former is both useful and seems relatively easy --

Re: allowing extensions to control planner behavior

On Wed, Aug 28, 2024 at 10:58 AM Robert Haas wrote: > Ever since I read > https://15721.courses.cs.cmu.edu/spring2020/papers/22-costmodels/p204-leis.pdf > I have believed that the cardinality misestimate leading to nested > loop plans is just because we're doing something dumb. > We don't even ha

Re: allowing extensions to control planner behavior

On Wed, Aug 28, 2024 at 8:37 AM Robert Haas wrote: > This has a big advantage over what I proposed yesterday in that it's > basically declarative. With one call to the hook, you get all the > information about the join order that you could ever want. That's > really nice. Hmm. On further thought,

Re: allowing extensions to control planner behavior

On Wed, Aug 28, 2024 at 9:46 AM Jakub Wartak wrote: > 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 genera

Re: allowing extensions to control planner behavior

Hi Robert, On Mon, Aug 26, 2024 at 6:33 PM Robert Haas 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 anythi

Re: allowing extensions to control planner behavior

On Tue, Aug 27, 2024 at 4:15 PM Tom Lane wrote: > 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 rearrang

Re: allowing extensions to control planner behavior

Robert Haas writes: > 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 Seems reasonable. It might be possible to say that our answer to "control over j

Re: allowing extensions to control planner behavior

Hi, On Tue, Aug 27, 2024 at 03:11:15PM -0400, Robert Haas wrote: > 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

Re: allowing extensions to control planner behavior

On Mon, Aug 26, 2024 at 1:37 PM Tom Lane wrote: > For example, I don't see > how this gets us any closer to letting an extension fix a poor choice > of join order. Thinking more about this particular sub-problem, let's say we're joining four tables A, B, C, and D. An extension wants to compel joi

Re: allowing extensions to control planner behavior

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

Re: allowing extensions to control planner behavior

Robert Haas 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

Re: allowing extensions to control planner behavior

On Tue, Aug 27, 2024 at 12:56 PM Tom Lane wrote: > Robert Haas writes: > > In that vein, here's a new patch set where I've added a second patch > > that allows extensions to control choice of index. > > I'm minus-several on this bit, because that is a solved problem and > we really don't need to

Re: allowing extensions to control planner behavior

Robert Haas writes: > In that vein, here's a new patch set where I've added a second patch > that allows extensions to control choice of index. I'm minus-several on this bit, because that is a solved problem and we really don't need to introduce More Than One Way To Do It. The intention has alwa

Re: allowing extensions to control planner behavior

On Tue, Aug 27, 2024 at 11:57 AM Joe Conway wrote: > On the one hand, excluding indexes by initial vowels is definitely > silly. On the other, I can see how it might be useful for an extension > to exclude indexes based on a regex match of the index name or something > similar, at least for testin

Re: allowing extensions to control planner behavior

On Tue, Aug 27, 2024 at 2:44 AM chungui.wcg wrote: > I really admire this idea. Thanks. > here is my confusion: Isn't the core of this idea whether to turn the > planner into a framework? Personally, I think that under PostgreSQL's heap > table storage, the optimizer might be better off focus

Re: allowing extensions to control planner behavior

On 8/27/24 11:45, Robert Haas wrote: On Mon, Aug 26, 2024 at 3:28 PM Robert Haas wrote: Well, I agree that this doesn't address everything you might want to do, ... I will very happily propose more things to address the other problems that I know about ... In that vein, here's a new patch set

Re: allowing extensions to control planner behavior

On Mon, Aug 26, 2024 at 3:28 PM Robert Haas wrote: > Well, I agree that this doesn't address everything you might want to > do, ... I will very happily propose more things to > address the other problems that I know about ... In that vein, here's a new patch set where I've added a second patch th

Re: allowing extensions to control planner behavior

On 26/8/2024 21:44, Robert Haas wrote: On Mon, Aug 26, 2024 at 2:00 PM Andrei Lepikhov wrote: My personal most wanted list: - Selectivity list estimation hook - Groups number estimation hook - hooks on memory estimations, involving work_mem - add_path() hook - Hook on final RelOptInfo pathlist

Re: allowing extensions to control planner behavior

On Mon, Aug 26, 2024 at 2:00 PM Andrei Lepikhov wrote: > It is the change I have been waiting for a long time. Remember how many > kludge codes in pg_hint_plan, aqo, citus, timescale, etc., are written > for only the reason of a small number of hooks - I guess many other > people could cheer such

Re: allowing extensions to control planner behavior

On Mon, Aug 26, 2024 at 1:37 PM Tom Lane wrote: > Robert Haas writes: > > 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. > > Nah, I won't flame you for that, it

Re: allowing extensions to control planner behavior

On 26/8/2024 18:32, Robert Haas 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. I know of two major reasons It is the change I have been waiting for a long time.

Re: allowing extensions to control planner behavior

Robert Haas writes: > 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. Nah, I won't flame you for that, it's a reasonable thing to think about. However, the devil is

allowing extensions to control planner behavior

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. I know of two major reasons why somebody might want to do this. First, you might want to do something like what pg_hint_pla