Partitioned object store lists all files on every query when using hive-partitioned parquet files

[henrifroese]

Describe the bug

Hey,

awesome project! I'm trying to use datafusion to query across 100s of thousands of partitioned parquet files in S3. Partitioning is e.g. experiment=A/measurement=B/date_id=20230101/file.parquet.

Now say I want to query SELECT * FROM table WHERE experiment='A' AND measurement='B' AND date_id=20230101.

Depending on what I do I get very different performance:

1. If I first register_listing_table with prefix experiment=A/measurement=B/ and only specify date_id in ListingOptions.with_table_partition_cols , and then send query SELECT * WHERE date_id=20230101 that's very fast.

2. If I just register_listing_table without prefix, and in ListingOptions.with_table_partition_cols specify experiment, measurement, date_id, and send the full query SELECT * WHERE experiment='A' AND measurement='B' AND date_id=20230101, that's very slow. (Still works!)

It makes sense to me that the first time, we have to list all files, so (2) is slower. But it's also slower on repeated queries.

Afaict the actual filtering happens in https://github.com/apache/arrow-datafusion/blob/cf0f8eececd37f593b811320f89c0edd00fd3945/datafusion/core/src/datasource/listing/helpers.rs#L328
which calls list_partitions.

I can think of two ways to speed this up:

1. Allow caching a ListingTable's files
2. In ListingTable, allow specifying the order of partitioning columns (e.g. in this case [experiment, measurement, date_id]), and if specified filters are a prefix of this, use that to only list beyond that prefix. E.g. here that'd lead to only listing files after experiment=A/measurement=B/.

Do any of these ways already exist and I just didn't find them? Or already have tickets?

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[henrifroese]

Ah seems to be exactly what @suremarc mentioned in this comment: #7620 (comment)

So #7620 added an optional file cache, but it's not used when using partitions.

[henrifroese]

Tried having a go at this in #9655 @suremarc @Ted-Jiang lmk whether that makes sense