Enable the `ListFilesCache` to be available for partitioned tables

[BlakeOrth]

Is your feature request related to a problem or challenge?

When using "high latency" storage (e.g. remote object stores, such as AWS S3) listing objects and collecting object metadata can end up taking a non-trivial amount of time. This has already been noted in #7618 where a simple cache was implemented and partially integrated. Unfortunately, partitioned tables cannot hit the cache path in the current ListingTable implementation. A discrepancy between the performance of flat vs partitioned tables has been previously noted in #9654. Unfortunately, that issue didn't provide much detail on the timing discrepancies, nor did it provide an easy way to reproduce the behavior.

In order to better illustrate this issue, I have added some basic timing statements in the ListingTable which can be reproduced using the diff below:

diff --git a/datafusion/core/src/datasource/listing/table.rs b/datafusion/core/src/datasource/listing/table.rs
index a93654cdf..8d1ddc909 100644
--- a/datafusion/core/src/datasource/listing/table.rs
+++ b/datafusion/core/src/datasource/listing/table.rs
@@ -1342,6 +1342,7 @@ impl ListingTable {
             return Ok((vec![], Statistics::new_unknown(&self.file_schema)));
         };
         // list files (with partitions)
+        let start = datafusion_common::instant::Instant::now();
         let file_list = future::try_join_all(self.table_paths.iter().map(|table_path| {
             pruned_partition_list(
                 ctx,
@@ -1353,6 +1354,10 @@ impl ListingTable {
             )
         }))
         .await?;
+        println!(
+            "file_list duration: {}ms",
+            1000. * start.elapsed().as_secs_f32()
+        );
         let meta_fetch_concurrency =
             ctx.config_options().execution.meta_fetch_concurrency;
         let file_list = stream::iter(file_list).flatten_unordered(meta_fetch_concurrency);
@@ -1370,8 +1375,17 @@ impl ListingTable {
             .boxed()
             .buffer_unordered(ctx.config_options().execution.meta_fetch_concurrency);

+        let group_start = datafusion_common::instant::Instant::now();
         let (file_group, inexact_stats) =
             get_files_with_limit(files, limit, self.options.collect_stat).await?;
+        println!(
+            "file_group duration: {}ms",
+            1000. * group_start.elapsed().as_secs_f32()
+        );
+        println!(
+            "Total duration: {}ms",
+            1000. * start.elapsed().as_secs_f32()
+        );

         let file_groups = file_group.split_files(self.options.target_partitions);
         let (mut file_groups, mut stats) = compute_all_files_statistics(

(note that reproducing these results currently relies on the resolution to /pull/17050)
The discrepancies between the timing for these operations can be seen below. In spite of the fact the queries ultimately end up operating on the same exact subset of the files, the queries issued to the partitioned dataset continue to take longer to complete. In all cases shown here the cumulative duration required to build the file_groups dominates the query times. Each query is issued twice to allow the default caching mechanisms in DataFusion to build cached data, and then operate on that cached data in the 2nd query.

DataFusion CLI v49.0.0
> set datafusion.execution.parquet.cache_metadata = true;
0 row(s) fetched.
Elapsed 0.000 seconds.

> CREATE EXTERNAL TABLE overture_maps
STORED AS PARQUET LOCATION 's3://overturemaps-us-west-2/release/2025-07-23.0/';
0 row(s) fetched.
Elapsed 7.724 seconds.

> select count(*) from overture_maps where type='address';
file_list duration: 317.549ms
file_group duration: 240.83353ms
Total duration: 558.39996ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.563 seconds.

> select count(*) from overture_maps where type='address';
file_list duration: 371.57742ms
file_group duration: 0.034617003ms
Total duration: 371.62094ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.376 seconds.

> CREATE EXTERNAL TABLE overture_maps_flat
STORED AS PARQUET LOCATION 's3://overturemaps-us-west-2/release/2025-07-23.0/theme=addresses/type=address/';
0 row(s) fetched.
Elapsed 1.478 seconds.

> select count(*) from overture_maps_flat;
file_list duration: 0.004915ms
file_group duration: 421.62354ms
Total duration: 421.64505ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.423 seconds.

> select count(*) from overture_maps_flat;
file_list duration: 0.004658ms
file_group duration: 42.788586ms
Total duration: 42.806194ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.044 seconds.

The basic layout of the underlying dataset can be seen here. Note that the subset of files accessed for the queries against the partitioned table will be exactly the same as the flat dataset.

$ aws s3 ls s3://overturemaps-us-west-2/release/2025-07-23.0/ --recursive
2025-07-22 14:42:50 1073537247 release/2025-07-23.0/theme=addresses/type=address/part-00000-57f746d8-98a1-4faa-94c2-4f084343ecac-c000.zstd.parquet
2025-07-22 14:43:06 1013189887 release/2025-07-23.0/theme=addresses/type=address/part-00001-57f746d8-98a1-4faa-94c2-4f084343ecac-c000.zstd.parquet
. . .

$ aws s3 ls s3://overturemaps-us-west-2/release/2025-07-23.0/theme=addresses/
                           PRE type=address/

The DefaultListFilesCache can be forced into the mix using the following diff:

diff --git a/datafusion/execution/src/cache/cache_manager.rs b/datafusion/execution/src/cache/cache_manager.rs
index 37f1baa17..1e3faad15 100644
--- a/datafusion/execution/src/cache/cache_manager.rs
+++ b/datafusion/execution/src/cache/cache_manager.rs
@@ -80,6 +80,9 @@ impl CacheManager {
         }
         if let Some(lc) = &config.list_files_cache {
             manager.list_files_cache = Some(Arc::clone(lc))
+        } else {
+            manager.list_files_cache =
+                Some(Arc::new(super::cache_unit::DefaultListFilesCache::default()));
         }
         if let Some(mc) = &config.file_metadata_cache {
             manager.file_metadata_cache = Some(Arc::clone(mc));

When the cache is inserted, the performance for the flat table shows significant improvements on repeat queries, however the partitioned table continues to suffer from latencies dominated by listing files.

DataFusion CLI v49.0.0
> set datafusion.execution.parquet.cache_metadata = true;
0 row(s) fetched.
Elapsed 0.000 seconds.

> CREATE EXTERNAL TABLE overture_maps
STORED AS PARQUET LOCATION 's3://overturemaps-us-west-2/release/2025-07-23.0/';
0 row(s) fetched.
Elapsed 9.056 seconds.

> select count(*) from overture_maps where type='address';
file_list duration: 412.9362ms
file_group duration: 272.7048ms
Total duration: 685.6636ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.690 seconds.

> select count(*) from overture_maps where type='address';
file_list duration: 136.47644ms
file_group duration: 0.034272ms
Total duration: 136.51955ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.141 seconds.

> CREATE EXTERNAL TABLE overture_maps_flat
STORED AS PARQUET LOCATION 's3://overturemaps-us-west-2/release/2025-07-23.0/theme=addresses/type=address/';
0 row(s) fetched.
Elapsed 0.727 seconds.

> select count(*) from overture_maps_flat;
file_list duration: 0.0063920002ms
file_group duration: 357.8303ms
Total duration: 357.86032ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.359 seconds.

> select count(*) from overture_maps_flat;
file_list duration: 0.0066520004ms
file_group duration: 0.030025ms
Total duration: 0.047092ms
+-----------+
| count(*)  |
+-----------+
| 446544475 |
+-----------+
1 row(s) fetched.
Elapsed 0.001 seconds.

Describe the solution you'd like

Ultimately I'd like partitioned datasets to operate with similar performance to flat datasets, and have caching mechanisms available to both. Based on the structure of the existing code, I believe that the code path the ListingTable executes when discovering files and collecting initial metadata should be normalized between partitioned datasets and flat datasets.

During a table scan, both table types first call list_files_for_scan followed by pruned_partition_list

datafusion/datafusion/core/src/datasource/listing/table.rs

Line 1356 in f3941b2

async fn list_files_for_scan<'a>(

datafusion/datafusion/catalog-listing/src/helpers.rs

Line 409 in f3941b2

pub async fn pruned_partition_list<'a>(

In pruned_partition_list flat tables end up executing list_all_files which is where the ListFilesCache is currently leveraged:

datafusion/datafusion/catalog-listing/src/helpers.rs

Lines 417 to 432 in f3941b2

	// if no partition col => simply list all the files
	if partition_cols.is_empty() {
	if !filters.is_empty() {
	return internal_err!(
	"Got partition filters for unpartitioned table {}",
	table_path
	);
	}
	return Ok(Box::pin(
	table_path
	.list_all_files(ctx, store, file_extension)
	.await?
	.try_filter(|object_meta| futures::future::ready(object_meta.size > 0))
	.map_ok(|object_meta| object_meta.into()),
	));
	}

Partitioned datasets, however, end up making calls that at least partially, and may fully rediscover, the partition and file structure by calling list_partitions

datafusion/datafusion/catalog-listing/src/helpers.rs

Lines 435 to 436 in f3941b2

	let partitions =
	list_partitions(store, table_path, partition_cols.len(), partition_prefix)

Note that list_partitions executes a breadth first search of the (potentially prefixed, depending on query filter evaluation) partition structure, and as such the per-query latency penalty it applies scales linearly with the depth of the partition tree.

datafusion/datafusion/catalog-listing/src/helpers.rs

Line 188 in f3941b2

pub async fn list_partitions(

My Opinion
Executing a breadth first search on the full, or a prefixed subset of, the dataset is likely less performant than simply recursively listing the full, or a prefixed subset of, dataset. The partitions of any given object can be directly derived from the object's path, allowing pruning of objects to continue working as it works right now. Additionally, any caching for listing all of the objects can then apply to both partitioned and flat tables. I suspect there are table structures where the current approach is better, such as a table with many many objects and a shallow partition structure. However, most remote object storage solutions will return many hundreds to thousands of object paths in a single list request, so the tables would need to be quite large in terms of number of objects.

Describe alternatives you've considered

It would probably be possible to apply a ListFilesCache prior to the divergence between the flat and partitioned tables. In this scenario I think care would need to be taken when loading objects into the cache, because partitioned datasets don't necessarily return all the potential objects due to pruning. This would also mean that repeat queries to partitioned datasets could continue to have a high percentage of cache misses depending on user supplied filters.

Additional context

Existing issues mentioned:

• [Cache] Support cache ListFiles result cache in session level #7618
• Partitioned object store lists all files on every query when using hive-partitioned parquet files #9654

Since this relates to the ListingTable and remote dataset:

• Improve performance of datafusion-cli when reading from remote storage #16365

[alamb]

Interestingly enough I was reviewing #17364 from @xiedeyantu and it seems like there is already code to look for the listing files cache

[BlakeOrth]

@alamb Yes, that is the case when the list_all_files method is called!
https://github.com/apache/datafusion/blob/main/datafusion/datasource/src/url.rs#L237

Unfortunately, this method is called conditionally on a table scan, which means the underlying table structure effectively determines whether or not it's possible to utilize the existing cache infrastructure. My proposed solution to this issue is to use list_all_files in all cases, so the existing cache infrastructure can be leveraged regardless of the underlying table structure.

Here's the code that shows the divergence in logic for when list_all_files is used, versus the other methods that are called:

datafusion/datafusion/catalog-listing/src/helpers.rs

Lines 417 to 438 in f3941b2

	// if no partition col => simply list all the files
	if partition_cols.is_empty() {
	if !filters.is_empty() {
	return internal_err!(
	"Got partition filters for unpartitioned table {}",
	table_path
	);
	}
	return Ok(Box::pin(
	table_path
	.list_all_files(ctx, store, file_extension)
	.await?
	.try_filter(|object_meta| futures::future::ready(object_meta.size > 0))
	.map_ok(|object_meta| object_meta.into()),
	));
	}
	let partition_prefix = evaluate_partition_prefix(partition_cols, filters);
	let partitions =
	list_partitions(store, table_path, partition_cols.len(), partition_prefix)
	.await?;
	debug!("Listed {} partitions", partitions.len());

[alamb]

Ultimately I'd like partitioned datasets to operate with similar performance to flat datasets, and have caching mechanisms available to both. Based on the structure of the existing code, I believe that the code path the ListingTable executes when discovering files and collecting initial metadata should be normalized between partitioned datasets and flat datasets.

This makes a lot of sense to me

[alamb]

What I am hoping to do is complete the work on the instrumented object store

• POC: datafusion-cli instrumented object store #17266

Which will then help us verify all the additional optimizations for listing table

[BlakeOrth]

Yes, I agree with this approach. Being able to effectively demonstrate a reduction in IO operations is important here, moreso than simply looking at query duration I think. Since remote object storage IO is heavily dependent on an individual user's situation (internet connection quality/speed, physical distance from the datacenter etc.) an increase in performance based strictly on query duration is potentially ambiguous.

[alamb]

Yes, I agree with this approach. Being able to effectively demonstrate a reduction in IO operations is important here, moreso than simply looking at query duration I think. Since remote object storage IO is heavily dependent on an individual user's situation (internet connection quality/speed, physical distance from the datacenter etc.) an increase in performance based strictly on query duration is potentially ambiguous.

yes 100%

[BlakeOrth]

@alamb Would you feel comfortable gathering some initial data for this effort using the existing code in

• POC: datafusion-cli instrumented object store #17266

even though it's still in draft? If so, I think we'd need this PR merged

• Auto detect hive column partitioning with ListingTableFactory / CREATE EXTERNAL TABLE #17232

so we can effectively interact with partitioned and flat tables, and then I'd be happy to update /pull/17266 and collect some data for us to improve our understanding of the existing request patterns.

[alamb]

@alamb Would you feel comfortable gathering some initial data for this effort using the existing code in

• POC: datafusion-cli instrumented object store #17266

even though it's still in draft? If so, I think we'd need this PR merged

Yes indeed

• Auto detect hive column partitioning with ListingTableFactory / CREATE EXTERNAL TABLE #17232

so we can effectively interact with partitioned and flat tables, and then I'd be happy to update /pull/17266 and collect some data for us to improve our understanding of the existing request patterns.

Yes sorry I forgot to merge that yesterday and I was trying to avoid causing issues with the datafusion 50 release

[BlakeOrth]

Ok, I've done some data collection using the in progress instrumented object store that illustrates some differences in request patterns. I'll share and go over the entire output, including the setup so it can be reproduced. Note that all of the following sections were run in a single datafusion-cli session, I'm just breaking it up a bit to make it easier to understand and discuss. One item to note is I'm running a debug build instead of release-nonlto because I think we're mostly interested in the IO operations, not necessarily whether or not they're running at optimized speeds.

Analysis

Table Setup

DataFusion CLI v50.0.0
> set datafusion.execution.listing_table_factory_infer_partitions=false;
0 row(s) fetched.
Elapsed 0.002 seconds.

> \object_store_profiling summary
ObjectStore Profile mode set to Summary
> CREATE EXTERNAL TABLE overture_flat
STORED AS PARQUET LOCATION 's3://overturemaps-us-west-2/release/2025-07-23.0/';
0 row(s) fetched.
Elapsed 34.223 seconds.

Object Store Profiling
List Summary:
  count: 1

List Summary:
  count: 1

Get Summary:
  count: 1536
  duration min: 0.045644s
  duration max: 0.593886s
  duration avg: 0.101914s
  size min: 8 B
  size max: 1328116 B
  size avg: 329311 B
  size sum: 505822728 B

> set datafusion.execution.listing_table_factory_infer_partitions=true;
0 row(s) fetched.
Elapsed 0.001 seconds.

Object Store Profiling
> CREATE EXTERNAL TABLE overture_partitioned
STORED AS PARQUET LOCATION 's3://overturemaps-us-west-2/release/2025-07-23.0/';
0 row(s) fetched.
Elapsed 35.500 seconds.

Object Store Profiling
List Summary:
  count: 1

List Summary:
  count: 3

Get Summary:
  count: 1536
  duration min: 0.042258s
  duration max: 0.314563s
  duration avg: 0.100344s
  size min: 8 B
  size max: 1328116 B
  size avg: 329311 B
  size sum: 505822728 B

This is the test case setup. In order to keep things as fair as possible, we can set up two tables using the same underlying data. One of the tables is a flat table with no partitions, the other lets the ListingTable operate on the partitions defined by the paths. Initially I'm using the summary mode for profiling due to the size of the underlying data, but even here we can already see some discrepancies between the flat and partitioned table emerge. In spite of the same number of GET requests, the partitioned table executes 2 additional LIST operations.

First queries

> select count(*) from overture_flat;
+------------+
| count(*)   |
+------------+
| 4198576839 |
+------------+
1 row(s) fetched.
Elapsed 36.787 seconds.

Object Store Profiling
List Summary:
  count: 1

Get Summary:
  count: 1536
  duration min: 0.044221s
  duration max: 0.390369s
  duration avg: 0.102023s
  size min: 8 B
  size max: 1328116 B
  size avg: 329311 B
  size sum: 505822728 B

> select count(*) from overture_partitioned;
+------------+
| count(*)   |
+------------+
| 4198576839 |
+------------+
1 row(s) fetched.
Elapsed 37.422 seconds.

Object Store Profiling
List Summary:
  count: 22
  duration min: 0.042043s
  duration max: 0.148611s
  duration avg: 0.096775s

Get Summary:
  count: 1536
  duration min: 0.045364s
  duration max: 0.375742s
  duration avg: 0.099633s
  size min: 8 B
  size max: 1328116 B
  size avg: 329311 B
  size sum: 505822728 B

These first queries really begin to show discrepancies. In spite of the GET requests being identical between the two queries, the partitioned table executes 22 LIST operations compared to the flat table's 1 LIST operation. Curiously, the GET requests appear to be identical between the table creation above and the first queries. It merits additional investigation, but the fact the minimum size for the GET is 8, I suspect this is fetching parquet footers and subsequently the metadata. This may point to an opportunity to seed the metadata cache earlier, which could improve performance for the first query to the ListingTable in general.

Subsequent Queries (cached metadata)

> \object_store_profiling trace
ObjectStore Profile mode set to Trace
> select count(*) from overture_flat;
+------------+
| count(*)   |
+------------+
| 4198576839 |
+------------+
1 row(s) fetched.
Elapsed 0.791 seconds.

Object Store Profiling
2025-09-09T17:53:07.108517313+00:00 operation=List path=release/2025-07-23.0
List Summary:
  count: 1

> select count(*) from overture_partitioned;
+------------+
| count(*)   |
+------------+
| 4198576839 |
+------------+
1 row(s) fetched.
Elapsed 0.943 seconds.

Object Store Profiling
2025-09-09T17:53:17.991269778+00:00 operation=List duration=0.142341s path=release/2025-07-23.0
2025-09-09T17:53:18.133630478+00:00 operation=List duration=0.042730s path=release/2025-07-23.0/theme=addresses
2025-09-09T17:53:18.133986930+00:00 operation=List duration=0.084354s path=release/2025-07-23.0/theme=base
2025-09-09T17:53:18.134506596+00:00 operation=List duration=0.109531s path=release/2025-07-23.0/theme=divisions
2025-09-09T17:53:18.218349909+00:00 operation=List duration=0.042024s path=release/2025-07-23.0/theme=base/type=bathymetry
2025-09-09T17:53:18.134958553+00:00 operation=List duration=0.128599s path=release/2025-07-23.0/theme=transportation
2025-09-09T17:53:18.134252722+00:00 operation=List duration=0.131287s path=release/2025-07-23.0/theme=buildings
2025-09-09T17:53:18.134744436+00:00 operation=List duration=0.135978s path=release/2025-07-23.0/theme=places
2025-09-09T17:53:18.176435159+00:00 operation=List duration=0.097157s path=release/2025-07-23.0/theme=addresses/type=address
2025-09-09T17:53:18.218771771+00:00 operation=List duration=0.075236s path=release/2025-07-23.0/theme=base/type=land
2025-09-09T17:53:18.218580227+00:00 operation=List duration=0.086389s path=release/2025-07-23.0/theme=base/type=infrastructure
2025-09-09T17:53:18.219139311+00:00 operation=List duration=0.087766s path=release/2025-07-23.0/theme=base/type=land_use
2025-09-09T17:53:18.219346240+00:00 operation=List duration=0.089985s path=release/2025-07-23.0/theme=base/type=water
2025-09-09T17:53:18.244078850+00:00 operation=List duration=0.070198s path=release/2025-07-23.0/theme=divisions/type=division
2025-09-09T17:53:18.244290215+00:00 operation=List duration=0.072455s path=release/2025-07-23.0/theme=divisions/type=division_area
2025-09-09T17:53:18.265775691+00:00 operation=List duration=0.071635s path=release/2025-07-23.0/theme=buildings/type=building_part
2025-09-09T17:53:18.218957344+00:00 operation=List duration=0.124570s path=release/2025-07-23.0/theme=base/type=land_cover
2025-09-09T17:53:18.263595467+00:00 operation=List duration=0.091649s path=release/2025-07-23.0/theme=transportation/type=connector
2025-09-09T17:53:18.244510237+00:00 operation=List duration=0.115401s path=release/2025-07-23.0/theme=divisions/type=division_boundary
2025-09-09T17:53:18.270813563+00:00 operation=List duration=0.093048s path=release/2025-07-23.0/theme=places/type=place
2025-09-09T17:53:18.263816680+00:00 operation=List duration=0.120937s path=release/2025-07-23.0/theme=transportation/type=segment
2025-09-09T17:53:18.265574453+00:00 operation=List duration=0.179032s path=release/2025-07-23.0/theme=buildings/type=building
List Summary:
  count: 22
  duration min: 0.042024s
  duration max: 0.179032s
  duration avg: 0.099650s

These final queries have been switched over to trace for the profiling now that metadata has been cached and the output won't be dominated by over 1500 GET requests. The difference in operation between the two tables is pretty clear here. Curiously, the LIST operations for the partitioned table are being emitted with a duration, which implies it's actually running list_with_delimiter() which has allowed the instrumented object store to collect accurate duration data!

Paying close attention to the timestamps here we can see the breadth first search listing strategy used on partitioned tables in action. First the root path is listed, blocking until the result is returned:

2025-09-09T17:53:17.991269778+00:00 operation=List duration=0.142341s path=release/2025-07-23.0

$17:53:17.991269778 + 0.142341s = 17:53:18.133610778$

Then, the first level of the table is listed (this is the partition of theme). This set of requests are executed in parallel, but block their respective next level in the search. To keep things brief I'll just show the math for theme=addresses.

2025-09-09T17:53:18.133630478+00:00 operation=List duration=0.042730s path=release/2025-07-23.0/theme=addresses
2025-09-09T17:53:18.133986930+00:00 operation=List duration=0.084354s path=release/2025-07-23.0/theme=base
2025-09-09T17:53:18.134506596+00:00 operation=List duration=0.109531s path=release/2025-07-23.0/theme=divisions
2025-09-09T17:53:18.134958553+00:00 operation=List duration=0.128599s path=release/2025-07-23.0/theme=transportation
2025-09-09T17:53:18.134252722+00:00 operation=List duration=0.131287s path=release/2025-07-23.0/theme=buildings
2025-09-09T17:53:18.134744436+00:00 operation=List duration=0.135978s path=release/2025-07-23.0/theme=places

$17:53:18.133630478 + 0.042730s = 17:53:18.176360478$

Finally the last set of directories get listed as their respective upstream list requests complete

2025-09-09T17:53:18.176435159+00:00 operation=List duration=0.097157s path=release/2025-07-23.0/theme=addresses/type=address
2025-09-09T17:53:18.218349909+00:00 operation=List duration=0.042024s path=release/2025-07-23.0/theme=base/type=bathymetry
2025-09-09T17:53:18.218771771+00:00 operation=List duration=0.075236s path=release/2025-07-23.0/theme=base/type=land
2025-09-09T17:53:18.218580227+00:00 operation=List duration=0.086389s path=release/2025-07-23.0/theme=base/type=infrastructure
2025-09-09T17:53:18.219139311+00:00 operation=List duration=0.087766s path=release/2025-07-23.0/theme=base/type=land_use
2025-09-09T17:53:18.219346240+00:00 operation=List duration=0.089985s path=release/2025-07-23.0/theme=base/type=water
2025-09-09T17:53:18.218957344+00:00 operation=List duration=0.124570s path=release/2025-07-23.0/theme=base/type=land_cover
... etc

Additionally, comparing the first LIST timestamp to the tail LIST timestamp and how long it took to complete, we can see that we spent 0.453s executing just listing operations of our total 0.943s query execution time.
$(17:53:18.265574453 + 0.179032s) - 17:53:17.991269778 = 0.453336675$

@alamb Given the above analysis, in the spirit of talking about this in terms of IO reduction, normalizing the IO pattern between partitioned and flat tables in this specific case would eliminate 21 LIST calls. Unfortunately, it's difficult to talk about this in absolutes because the number of LIST calls for partitioned tables depends on the table structure (and actually on the query run as well, but I think this initial analysis is already long enough). This data also suggests that if a ListFilesCache were to be implemented and enabled it could eliminate all the IO for simple queries that can be computed entirely from metadata.

[alamb]

These first queries really begin to show discrepancies. In spite of the GET requests being identical between the two queries, the partitioned table executes 22 LIST operations compared to the flat table's 1 LIST operation.

Yes this seems like a quite compelling reason to:

1. Cache the listing results
2. Get the datafusion-cli listing feature polished up.

This data also suggests that if a ListFilesCache were to be implemented and enabled it could eliminate all the IO for simple queries that can be computed entirely from metadata.

This sounds great and a worthy goal for sure

[BlakeOrth]

If we'd like to move forward with some initial plans or implementations I'm happy to contribute in whatever way I can to help move the effort forward, just let me know what would be the most helpful.

My personal approach would be to start with normalizing the code paths between flat vs partitioned tables so any existing/future caching work can apply to both without additional work or furthering the discrepancies between the two. However, given some list caching infrastructure already exists, I could also see justification for building out a default listing cache first. Based on my current code exploration I don't think the two are very entwined, so it's possible the efforts could proceed as parallel efforts with minimal conflicts.