Naveena avatar image
Naveena asked Erick Ramirez edited

Seeing read performance issues with queries where ALLOW FILTERING is enabled

Hi, we have a Cassandra table with Partition and Cluster key defined. And Select query is having where clause on Partition Key. But we are seeing performance issue while reading data from table when load increased on the table.

1. I could see that 'Allow Filtering' is enabled. planning to disable that as it doesn't make sense to use when where clause is having filter on partition key. Is it correct thing to do?

2. we are not looking at any data sorting. Is it required to have Cluster key defined on the table? does it create any performance issues with unnecessarily having Cluster key defined?

Please find table schema.

Column1 Text Partition Key
Column2 Text Cluster Key
Column3 Text
Column4 Text
Column5 Timestamp
Column6 Timestamp

Query -

Select * from Table1 where Column1=' ' ALLOW FILTERING;

I cant provide exact column names due to security reasons, instead renamed Table and Column names.

Hope details will be helpful.

Could you please help in performance improvement options? Thanks in advance.

10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez edited

You haven't posted enough information for us to determine what the problem is. For starters, please update your original question with the following:

  • table schema
  • sample query

To respond to your questions directly:

  1. We don't have enough information so we need more details.
  2. No, clustering keys are not mandatory but it has nothing to do with read performance so I think you're confusing things.

[UPDATE] Based on the query you've posted, ALLOW FILTERING won't matter since the filter is already on the partition key.

Removing ALLOW FILTERING won't make a difference to the read performance because the query is not filtering on any other column other than the partition key.

Given your limited ability to provide information for security reasons, it would be difficult to determine why you are seeing read performance issues. The only thing I could suggest is to have a look at our Luna offering so you can get one-to-one support assistance for Apache Cassandra. Cheers!

10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

ion_145132 avatar image
ion_145132 answered

#1. Yes, it is the correct thing to do by disabling ALLOW FILTERING, more than that - NEVER USE IT AGAIN. I doubt the performance issue is caused by ALLOW FILTERING in this case because you are querying by partition key anyway. I would look closely at your table model, make sure your partitions have appropriate sizes, no more than ~100MB.

10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.