cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

OPTIMIZE with liquid clustering makes filter slower than without OPTIMIZE

SankaraiahNaray
New Contributor II

I created 15 Million records as a Delta Table and i'm running a simple filter query on that table based on one column value - which will return only one record. Because all the values on that column are unique.

Delta Table is not partitioned.

Before enabling Liquid clustering/OPTIMIZE, the query response time was less than a second.

After enabling Liquid Clustering/OPTIMIZE, the query takes 3 to 4 seconds.

If i just enabled Liquid Clustering without OPTIMIZE, then the query response time is less than a second.

What is going on here?

 

4 REPLIES 4

-werners-
Esteemed Contributor III

is the column you query clustered by LQ or not? That could be the explanation.

Yes the column is used as Clustering Key

-werners-
Esteemed Contributor III

it seems that for this specific query Liquid Clustering has worse performance.  It does not have better performance for all queries.

The following are examples of scenarios that benefit from clustering:

  • Tables often filtered by high cardinality columns.

  • Tables with significant skew in data distribution.

  • Tables that grow quickly and require maintenance and tuning effort.

  • Tables with concurrent write requirements.

  • Tables with access patterns that change over time.

  • Tables where a typical partition key could leave the table with too many or too few partitions.

I'm testing a scenario mentioned in the document ( nothing complex)

The filter i'm using is High Cardinality column(every record is unique) and my table is not partitioned (so this is straight forward scenario)

  • Tables often filtered by high cardinality columns.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!