Add `auto_explain` mode

[nuno-faria]

Is your feature request related to a problem or challenge?

It would be useful to collect plans from queries automatically, without needing to explicitly add EXPLAIN ANALYZE. This would ensure that the original application would not need to change, as the queries would still return data as normal, while the plans would be printed to stdout/stderr.

Some existing systems already support this feature, like auto_explain in Postgres and eqp in SQLite.

Describe the solution you'd like

Some datafusion.execution.auto_explain config that would enable this feature.

I already created a small proof of concept and this feature would be relatively easy to implement. We just need to wrap execution plans in an AnalyzeExec. This AnalyzeExec would also need a flag to know when it's in the auto_explain mode, in which case it would print the plans and return the input batches.

I would be happy to create a PR with this.

Describe alternatives you've considered

Adding EXPLAIN ANALYZE manually, but this changes the application.

Additional context

No response

[alamb]

@2010YOUY01 and @NGA-TRAN have also been working in this area -- they may have some ideas here

This could also potentially be a datafusion-cli only feature...

[NGA-TRAN]

The idea sounds good to me. Thanks @nuno-faria

[carpecodeum]

Hi @NGA-TRAN @2010YOUY01 , I am getting started with Datafusion, and this issue seems exciting. Can I help you guys in any way?

[nuno-faria]

@carpecodeum thanks for the offer. Right now I have an implementation ready which I need to cleanup / add some tests before creating a PR, which I will try to get done soon. So for this issue there isn't much to do right now, but I will tag you once the PR is ready so you can help in the review if you want.

[alamb]

Thanks @nuno-faria

Form my perspective, having an "auto explain" feature is super valuable. However, writing to stdout / stderr as proposed in #19316 doesn't make sense to put in the core datafusion library because:

1. DataFusion can be embedded, and runs on many different environments where stdout may be being used for something else (e.g. logging).

Instead, it seems to me that auto explain belongs in the client applications themselves (e.g. datafusion-cli)

That being said, I think it is a valuable feature for many client applications, so including help / support in the core or perhaps as an example makes sense. Specifically I think it is important to separate the logic of where the output will go.

Perhaps some sort of Observer pattern:

trait PlanObserver {
  fn planned(id: ..., plan: &Arc<dyn ExecutionPlan>);
  fn plan_executed(id: ..., plan: &Arc<dyn ExecutionPlan>);
}

let observer = ... // some sort of trait object that can receive explain plans

let ctx = SessionContext::new(....)
.with_plan_observer(Arc::new(explain_output));

// And now the callback in explain_output is invoked whenever the plan is run

Then, `datafusion-cli` could implement the observer if/when necessary

This design would also make it easy to capture post execution metrics as well, for example