Hello, Thank you for sharing the files. I assume the intermediate costs were included, as the cost is updated on each step for many nodes. So in step 89, #197-BindableJoin has a CPU cost of 6.787504586323E12, and that cost gets updated to 5.6541574E7 in step 90. However, its parent subset#170 does not get updated, contrary to the subsets on the path from the new node in step 90, #318. (See [1], [2]) It seems that there's indeed a problem with updating the costs in that scenario.
Could you debug VolcanoPlanner.propagateCostImprovements(RelNode) for step 90, to see why the propagation does not apply to subset#170? [1] https://raw.githack.com/thomasrebele/calcite-relsubset-foo/main/job_query_26b/planner-viz/planner-vizrule-match-viz.html?step=89&dir=0 [2] https://raw.githack.com/thomasrebele/calcite-relsubset-foo/main/job_query_26b/planner-viz/planner-vizrule-match-viz.html?step=90&dir=0 Cordialement / Best Regards, *Dr. Thomas Rebele* | R&D Developer | Germany | *E* *treb...@tibco.com <treb...@tibco.com>* | *W* *www.cloud.com <http://www.cloud.com/>* *Cloud SG Germany GmbH* | ℅ Citrix Systems GmbH, Erika-Mann-Straße 67-69, 80636 München Deutschland | Registergericht: Amtsgericht München, HRB 123355 | Geschäftsführer: Antonio Gomes, Oliver Ebel, Ganesh Vaidyanathan, Brian Lee Shytle On Wed, Jan 24, 2024 at 9:51 PM Tony Fiedler < tony.fied...@mailbox.tu-dresden.de> wrote: > Hello, > > Right, I guess I understand what the color encoding means. Thanks for > confirming my thoughts. > > >> If I remember correctly, the cost for a subset shown at a step should be > >> the same as the best cost of all children for that particular step. > > Right and this is what confuses me since this doesn't always be to seem > the case -- for simple queries this seem to work perfectly fine. > > >> It would be helpful to share at least the generated files (especially > the > >> .js), to understand what's going on. > > Makes total sense.. There you go [1]. But be warned, the operator tree > is rather big since I throw the Join Order Benchmark queries [2] from > Leis et al. *How Good Are Query Optimizers, Really?* against it. Those > contain many (10+) join operations. In this case the tree originates > from query 26b. Unfortunately, I wasn't able to reproduce this cost > calculation behaviour for less complex queries. > > [1]: > > https://github.com/AES-256-GCM/calcite-relsubset-foo/tree/main/job_query_26b/planner-viz > [2]: http://www-db.in.tum.de/~leis/qo/job.tgz > > Kind regards, > Tony > > On 2024/01/16 16:49:13 Thomas Rebele wrote: > > Hello, > > > > The RuleMatchVisualizer uses the planner to get the cost [1], and the > > Volcano planner uses the bestCost attribute for RelSubset [2]. > > > > The color depends on the steps: > > * For intermediate steps, the purple color shows which nodes have been > > matched by the rule. Light blue shows added nodes. > > * For the final step, the purple and light blue colors show the chosen > > nodes of the final plan. Light blue for the RelSubset nodes. > > > > If I remember correctly, the cost for a subset shown at a step should be > > the same as the best cost of all children for that particular step. > > > > It would be helpful to share at least the generated files (especially the > > .js), to understand what's going on. > > > > [1] > > > https://github.com/apache/calcite/blob/c4042a34ef054b89cec1c47fefcbc8689bad55be/core/src/main/java/org/apache/calcite/plan/visualizer/RuleMatchVisualizer.java#L300C34-L300C34 > > [2] > > > https://github.com/apache/calcite/blob/c4042a34ef054b89cec1c47fefcbc8689bad55be/core/src/main/java/org/apache/calcite/plan/volcano/VolcanoPlanner.java#L722 > > > > Cordialement / Best Regards, > > *Dr. Thomas Rebele* | R&D Developer | Germany | *E* *treb...@tibco.com > > <tr...@tibco.com>* | *W* *www.cloud.com <http://www.cloud.com/>* > > > > *Cloud SG Germany GmbH* | ℅ Citrix Systems GmbH, Erika-Mann-Straße > 67-69, > > 80636 München Deutschland | Registergericht: Amtsgericht München, HRB > > 123355 | Geschäftsführer: Antonio Gomes, Alexander E. Kolar, Ganesh > > Vaidyanathan, Brian Lee Shytle > > > > > > > > On Fri, Jan 12, 2024 at 11:35 PM Tony Fiedler < > > tony.fied...@mailbox.tu-dresden.de> wrote: > > > > > Dear Calcite devs, > > > > > > First of all I really appreciate having a mature framework like > Calcite. > > > Please continue your great work on this project! > > > > > > My use case is feeding Calcite (v1.35.0) with an SQL query and doing > > > some optimizations by providing metadata and selected planner rules. I > > > initialize the Volcano planner and convert the logical plan resulting > > > from the sql to a physical plan (using bindable convention). > > > After the optimization, I convert the physical plan back to sql -- > > > hoping its execution time is faster (running the query by a PostgreSQL > > > server) than the original query. > > > > > > There are some aspects I don't understand regarding both the cost > > > calculation and cost propagation of (Rel) Subsets in the tree-based > plan > > > representation generated by RuleMatchVisualizer. > > > > > > AFAIK Subsets don't have any costs [1], so I'm really confused why > > > (cumulative) `cpu` is higher in the subset than it is in its child > > > elements (BindableJoin and BindableFilter), see [2]. In addition to > that > > > the cost metric `rows` is smaller(!) than the values provided by the > > > children. > > > > > > What I expect is that Subset has exactly the same `rows`, `cpu` (and > > > `io`) of the selected (purple) child element. > > > Having a look at this sub tree [3] the cost propagation works like > > > expected. > > > > > > Besides that I already noticed that Calcite costs seem to have an upper > > > bound (9.223372036854775807E18) where costs can't get any higher in sub > > > trees where this value is reached in an (physical operator) element. > > > > > > I know it's hard to tell what Calcite actually does just using > > > screenshots. Please let me know if I should provide e.g., my code for > > > giving better insights. > > > > > > Thank you in advance for your reply! > > > > > > [1]: > > > > > > > https://github.com/apache/calcite/blob/c4042a34ef054b89cec1c47fefcbc8689bad55be/core/src/main/java/org/apache/calcite/plan/volcano/RelSubset.java#L254 > > > [2]: https://ibb.co/7jtXKH3 > > > [3]: https://ibb.co/5BZZyLz > > > > > > Best regards, > > > Tony > > > > > >
