SedonaDB doesn't use the column statistics of `bbox` column?

[yutannihilation]

When I was trying to do the same thing as the DuckDB SQL in Overture Maps' docs, I found SedonaDB is strangely slow compared to DuckDB.

Benchmark results (seconds):

Engine	Median	Mean	Min	Max	Runs	row_count	max_confidence
DuckDB	0.588484	2.243619	0.585020	5.557354	3	7471	0.999544084072
SedonaDB	54.524910	55.320437	51.254931	60.181470	3	7471	0.999544084072

This is the script for benchmarking: yutannihilation@a2c5a8f

I suspect it's because SedonaDB doesn't utilize the statistics of the bbox column for pushdown, because it's faster (but still 10x slower than DuckDB) if I replace these conditions on bbox with ST_Intersects(geometry, ...). I know this DuckDB SQL is a temporary one because bbox column will disappear, but I'm wondering if this is an expected result.

  WHERE
    categories.primary = 'pizza_restaurant'
    AND bbox.xmin BETWEEN -75 AND -73
    AND bbox.ymin BETWEEN 40 AND 41

[paleolimbot]

You're correct that SedonaDB will push down WHERE ST_Intersects(ST_GeomFromWKT('...', 4326)) automatically using bounding box statistics in the bbox column of a GeoParquet file; however, DataFusion doesn't support pruning on struct column fields so we can't either (it will be added to the forthcoming DataFusion 53). You can check if pruning happened by running EXPLAIN ANALYZE (query) and checking the bottom right column (usually I need to collect first for overture because the result is long enough that it causes display to be truncated).

If the pruning did occur, I suspect that WHERE ST_Intersects(ST_GeomFromWKT('...', 4326)) is slower because DuckDB does a better job caching remote files/metadata/file listings. You can check if the difference is because of this by using SET enable_external_file_cache = false; in DuckDB. There are some improvements in DataFusion 52 to help there and also #294 , which at the time the PR was opened didn't help but perhaps does now.

[yutannihilation]

Thanks for the details!

SedonaDB will push down WHERE ST_Intersects(ST_GeomFromWKT('...', 4326)) automatically using bounding box statistics in the bbox column of a GeoParquet file

Ah, thanks for the explanation. I think I learned how this works when I read you blog post, but completely forgot...

DataFusion doesn't support pruning on struct column fields so we can't either (it will be added to the forthcoming DataFusion 53)

Good to know!

DuckDB does a better job caching remote files/metadata/file listings.

I didn't realize the metadata caching matters this much. I'll try SET enable_external_file_cache = false later

[yutannihilation]

If I use SET enable_external_file_cache = false, it takes almost the same. So, I guess it explains the difference.

Benchmark results (seconds):

Engine	Median	Mean	Min	Max	Runs	row_count	max_confidence
DuckDB	5.445727	5.547052	5.390490	5.804940	3	7471	0.999544084072
SedonaDB	4.724287	4.868763	4.718935	5.163067	3	7471	0.999544084072

[paleolimbot]

Thanks for that...I have long suspected this but have never been sure. I'll revisit #294 this week to see if we can make repeated queries any faster.

[yutannihilation]

Looking forward to seeing the result!

[yutannihilation]

Closed by #646