Enable `split_file_groups_by_statistics` by default

[alamb]

Is your feature request related to a problem or challenge?

• Part of [Epic] A Collection of Sort Based Optimizations #10313

In #9593, @suremarc added a way to reorganize input files in a ListingTable to avoid a merge, if the sort key ranges do not overlap

This feature is behind a feature flag, split_file_groups_by_statistics which defaults to false as I think there needs to be some more tests in place before we turn it on

Describe the solution you'd like

Add additional tests and then enable split_file_groups_by_statistics by default

Describe alternatives you've considered

No response

Additional context

No response

[alamb]

Example test coverage we should add I think: #9593 (comment)

[yyy1000]

I'd like to help it. 🙌

[alamb]

THank you @yyy1000 🙏

I think a good place to start would be to write some sqllogic level tests to cover the important cases

Perhaos for the first test:

1. Create files: file1.parquet, file2.parquet both sorted on a but file 1 has the columns in the order a, b, c and file has the columns in the order c, b, a. The keyranges of values of a should be non overlapping
2. Create an external table a, b, c with explicit order by a, and then query SELECT ... ORDER BY a and make sure the output plan doesn't use sort preserving merge

I think we could extend https://github.com/apache/datafusion/blob/main/datafusion/sqllogictest/test_files/parquet_sorted_statistics.slt

cc @suremarc

[leoyvens]

One thing I've noticed is that after DataFusion 40 this actually works in my use case, likely thanks to the statistics code getting fixed, so good news there! It does require additionally setting execution.collect_statistics = true, which makes sense.

However for my entirely sorted and non-overlapping dataset it did make Parquet scanning single-threaded (ParquetScan with a single file group), which is a big performance regression. So it didn't really help me, maybe I actually want #10316.

The consequence to this issue being that turning this on by default would regress performance for users that have execution.collect_statistics = true. Maybe the flag should be merged with prefer_existing_sort, which has the semantics of avoiding sorts at the cost of limiting parallelism. Or maybe just wait for #10316, so we can both avoid the sort and still have a parallel ParquetExec.

[alamb]

Sorry for the delay @leoyvens and thank you for this analysis

#11170

I would personally love to take this approach

[suremarc]

Leaving some thoughts here as I was asked in another issue about what it would take to turn this feature on, and I don't want to take over that thread --

1. As pointed out in @leoyvens's comment, the current impl is too aggressive about limiting parallelism, so we will need to modify the first fit algorithm to distribute into at least k groups. This can maybe be done if we initialize k empty groups and then use some sort of priority queue in the first fit algorithm that prioritizes the smallest groups.
2. I am worried about the performance of this feature for large ListingTables with tens or hundreds of thousands of files. Collecting statistics for that many files and sorting by the minimum values is going to take up a measurable amount of planning time at that scale, so we may cause a performance regression. IMO, we need to set up a benchmark on a large ListingTable to quantify the impact.

[xudong963]

Fyi, I'm working on it.