Search code examples
cassandralimit

Cassandra LIMIT 1 is not optimized

CREATE TABLE IF NOT EXISTS details (
    public_id text,
    updated_at timeuuid,
    details text,
    PRIMARY KEY (public_id, updated_at)
) WITH CLUSTERING ORDER BY (updated_at DESC)
AND comment='A table to hold details update history'

public_id - PartitionKey - String (uuid)
updatedAt - clusteringKey - timeuuid
details - json, size ranges from 1KB - 4 KB

Query - select * from details where public_id = 'prefix_4758a709fae94c87a84ea89623037577' limit 1;

I looked at the article - https://thelastpickle.com/blog/2017/03/07/The-limit-clause-in-cassandra-might-not-work-as-you-think.html

As the article mentioned, this was fixed in 3.4 - https://issues.apache.org/jira/browse/CASSANDRA-8180

My version - Cassandra 4.0.5

But looking at the trace output below, it seems to be not working, as you can see

Merged data from memtables and 19 sstables

Merged data from memtables and 21 sstables

enter image description here enter image description here

Here is tracing for another partition

  • speculative retry to 3 nodes
  • In total with RF 3 and CL= LOCAL_QUORUM, 6 nodes been hit, weird

enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here

Solution

  • https://issues.apache.org/jira/browse/CASSANDRA-19345 Looks like, regressed and lost the optimization from CASSANDRA-8180