Plan joins with PartitionMode::Auto by default #15349

Is your feature request related to a problem or challenge?

Currently PartitionMode::Partitioned is the default when statistics collection is not used.
This lead to suboptimal plans whenever CollectLeft could be used based on file or plan-derived (e.g. limit 10)) statistics.

Describe the solution you'd like

Use PartitionMode::Auto and let optimizer figure out if statistics can be used.

Describe alternatives you've considered

No response

Additional context

No response