topic Re: Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimizatio in Data Engineering

Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimization?

laudhon — Fri, 14 Jun 2024 11:17:07 GMT

Hello,

I have a large Delta table with a size of 29TB. I implemented Liquid Clustering on this table, but running a simple MIN MAX query on the set cluster column is still extremely slow. I have already optimized the table. Am I missing something in my implementation?

Re: Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimizatio

jacovangelder — Fri, 14 Jun 2024 11:59:47 GMT

What is the data type of the field you're querying?
All I can see is the name "_PartitionColumnUTC_". Judging by the name it is a date/timestamp but this is me making assumptions.

Re: Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimizatio

LuisRSanchez — Fri, 14 Jun 2024 14:15:30 GMT

this operation should take seconds because it use the precomputed statistics for the table. Then few elements to verify:

if the data type is datetime or integer should work, if it is string data type then it needs to read all data.
verify the column position, normally delta lake only create statistics for the some columns (i think first 15), if the column is not at the list of column to precompute stats then it needs to read all data.

if you need to read all data, then i saw in the image that only 4 task are running means 4 cores, then i would recommend to use a bigger cluster in memory and cores (scale up) with fewer nodes to reduce the shuffle.

Re: Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimizatio

laudhon — Sat, 15 Jun 2024 06:22:16 GMT

Hello, this is a type integer in the format YYYYMMDD

Re: Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimizatio

jacovangelder — Sat, 15 Jun 2024 18:45:39 GMT

That is strange, min/max of integers should be able to be retrieved very quickly, especially if they are partitioned columns. You are 100% sure it is an integer column and not a string? You didn't specify any filter clauses in your queries that would potentially trigger a full table scan?

Re: Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimizatio

laudhon — Thu, 20 Jun 2024 06:20:52 GMT

Hi Jaco,

Using the ANALYZE TABLE command fixed the issue; however, I am still experiencing very slow queries on the STRING type of a different cluster key. Does liquid clustering not support the STRING type very well?

Re: Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimizatio

jacovangelder — Thu, 20 Jun 2024 06:31:23 GMT

Ah then your table had to have its statistics refilled, glad it works now.
As for string types, it should work just as well.
"slow" is a bit subjective maybe. You have not yet mentioned the warehouse tier/cluster config, are you using sufficient processing power?