Query Performance improvement in Cassandra

Question

I have a table in my Cassandra db.

CREATE TABLE table (
    pk uuid,
    status int,
    location text,
    type text,
    id  text,
updatedtimestamp timestamp, 
        PRIMARY KEY (pk)
);

CREATE INDEX  tablelocation ON table (location);
CREATE INDEX  tabletype ON table (type);
CREATE INDEX  tableid ON table (id);
CREATE INDEX  tableupdatedtimestamp ON table (updatedtimestamp);

The query I run is :

Select * from table 
where location='A1' 
and type='T1' 
and status=001 
and id='NA' 
allow filtering;

Cassandra is taking more than 5 seconds to return 4000 records for this query. I already have secondary index on all of these columns. As per the DBA, issue is in id='NA' condition. There are too many rows where this condition is true.

But, that condition is there due to a business use case and can not be removed without some other mechanism to filter for the value.

I was thinking of creating a new index with all 4 columns inside. But, I am worried that it will hamper the write performance. The status column gets updated very frequently.

Is there anything we can do to tune performance of this query?

Answer 1

Indexing with Cassandra will never be performant, because it wasn't designed to be. 4000 rows for Cassandra isn't a big deal. But query 4000 rows across all nodes in the cluster, and now you've added network time into the equation.

If you want this to perform, what you need to do is to build a table to support this query. Specifically, that means designing a primary key structure so that the query can be served by a single node.

Depending on the cardinalities of location and type, you might try something like this:

CREATE TABLE table_by_location_type (
    pk uuid,
    status int,
    location text,
    type text,
    id  text,
    updatedtimestamp timestamp, 
    PRIMARY KEY ((location,type),id,pk)
);

CREATE INDEX  tablestatus ON table_by_location_type (status);

This will partition your data by location and type, guaranteeing that data on that column combination will be stored together. Next, this clusters your data (within each partition by id), and adds the pk at the end to ensure uniqueness. An index on status is ok as long as it is used together with location and type.

Pro-tips:

• Build your tables to suit your queries. Not the other way around.
• If you need to support multiple query patterns, build a second table and duplicate your data into it.
• Secondary indexes were designed for convenience, not performance. Avoid them, unless you're providing the complete partition key.
• Never, ever use the ALLOW FILTERING directive in production.

Answer 2

According to the Casandra CQL documentation for ALLOW FILTER:

By default, CQL only allows select queries that don’t involve a full scan of all partitions. If all partitions are scanned, then returning the results may experience a significant latency proportional to the amount of data in the table. The ALLOW FILTERING option explicitly executes a full scan. Thus, the performance of the query can be unpredictable.

^{Reference: Data Manipulation (Cassandra Documentation)}

So remove the ALLOW FILTERING directive from your SELECT... statement and see if the performance improves.

This has also been pointed out by @Aaron in his answer:

• Never, ever use the ALLOW FILTERING directive in production.

I just added the details form the official documentation.