Cassandra - querying on clustering keys

Question

I am just getting start on Cassandra and I was trying to create tables with different partition and clustering keys to see how they can be queried differently.

I created a table with primary key of the form - (a),b,c where a is the partition key and b,c are clustering key.

When querying I noticed that the following query:

select * from tablename where b=val;

results in:

Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING

And using "ALLOW FILTERING" gets me what I want (even though I've heard its bad for performance).

But when I run the following query:

select * from tablename where c=val;

It says:

PRIMARY KEY column "c" cannot be restricted (preceding column "b" is either not restricted or by a non-EQ relation)

And there is no "ALLOW FILTERING" option at all.

MY QUESTION IS - Why are all clustering keys not treated the same? column b which is adjacent to the partition key 'a' is given an option of 'allow filtering' which allows querying on it while querying on column 'c' does not seem possible at all (given the way this table is laid out).

ALLOW FILTERING gets cassandra to scan through all SSTables and get the data out of it when the partition key is missing, then why cant we do the same column c?

Answer 1

It's not that clustering keys are not treated the same, it's that you can't skip them. This is because Cassandra uses the clustering keys to determine on-disk sort order within a partition.

To add to your example, assume PRIMARY KEY ((a),b,c,d). You could run your query (with ALLOW FILTERING) by specifying just b, or b and c. But it wouldn't allow you to specify c and d (skipping b) or b and d (skipping c).

And as a side node, if you really want to be able to query by only b or only c, then you should support those queries with additional tables designed as such. ALLOW FILTERING is a band-aid, and is not something you should ever do in a production Cassandra deployment.