Getting ReadTimeout with COUNT() and AVG()

Question

ReadTimeout: Error from server: code=1200 [Coordinator node
    timed out waiting for replica nodes' responses] message=
    "Operation timed out for guitardb.active_line_status_new
    - received only 0 responses from 1 CL=ONE." info=
    {'received_responses': 0, 'required_responses': 1, 
    'consistency': 'ONE'}

I have a table with 900,000 records while aggregation, count and avg between two timestamp values. I'm facing a ReadTimeout issue.

select sum(unit_price) AS line_total  
from active_line_status_new 
WHERE order_createts>='2022-10-01 00:00:00' 
AND order_createts<='2022-10-27 23:59:59'  
ALLOW FILTERING;

Here I have created index on order_createts column which is used in where clause as well. I have only one node in the cluster.

Answer 1

You didn't provide the CQL query you ran but I assume you are doing an unbounded SELECT then aggregating the results.

If so, timeouts are expected when performing expensive queries which have a scatter-gather access pattern or the equivalent of a full table scan because Cassandra needs to retrieve ALL the partitions from ALL the nodes in order to aggregate them into a single result.

Cassandra is designed for high-velocity access, retrieving one partition at a time at very low latencies. Retrieving all the records of a table might work for a few thousand records on single-node clusters but it doesn't scale when (a) there are lots of partitions, (b) lots of nodes, or (c) both.

If you are interested, I've explained in more detail in Why is COUNT() bad in Cassandra?. Cheers!