topic Re: OPTIMIZE with liquid clustering makes filter slower than without OPTIMIZE in Data Engineering

OPTIMIZE with liquid clustering makes filter slower than without OPTIMIZE

SankaraiahNaray — Wed, 20 Mar 2024 11:07:18 GMT

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?

Re: OPTIMIZE with liquid clustering makes filter slower than without OPTIMIZE

-werners- — Thu, 21 Mar 2024 09:04:39 GMT

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

Re: OPTIMIZE with liquid clustering makes filter slower than without OPTIMIZE

SankaraiahNaray — Thu, 21 Mar 2024 10:50:48 GMT

Yes the column is used as Clustering Key

Re: OPTIMIZE with liquid clustering makes filter slower than without OPTIMIZE

-werners- — Mon, 25 Mar 2024 15:15:12 GMT

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.

Re: OPTIMIZE with liquid clustering makes filter slower than without OPTIMIZE

SankaraiahNaray — Mon, 25 Mar 2024 15:21:23 GMT

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.