Is there a way to compare the physical execution in terms of their costs?

[metesynnada]

I am seeking a method to evaluate various execution plans that yield the same results, specifically in the context of their execution costs. Initially, I considered using Statistics output as a benchmark, even though I understand that it may not be entirely accurate. Nevertheless, I believe it could be a viable starting point. Are there any recommendations on this

[alamb]

I don't have any recommendations -- the join selection pass has a very simplistic model of costing different options that might be interesting:

https://github.com/apache/arrow-datafusion/blob/main/datafusion/core/src/physical_optimizer/join_selection.rs

[metesynnada]

I think we are not yet there, however cost-based optimization is quite important for many use cases, and hope we can implement it soon.

[alamb]

I think we are not yet there, however cost-based optimization is quite important for many use cases, and hope we can implement it soon.

It is important, I agree.

However, it also has many known challenges too (like cardinality estimation error's multiplying and and various forms of skew).

As I understand it, the current "best practice" is to try and make as many choices at runtime as possible when the actual statistics are known -- though I don't know of any great way to do this for joins