Allow filter in cassandra query

Question

I have one table

CREATE TABLE user (
    p1 int,
    p2 int,
    created_at timestamp,
    user_name text,
    c1 text,
    PRIMARY KEY ((p1, p2),user_name)
) WITH additional_write_policy = '99p';

what will happen if i will run query

select * from user where p1 = 1 and p2 = 1 and user_name = 'abc' allow filtering;

will allow filtering will work here or it's just redundant in query .

Answer 1

Since you're filtering by the primary key columns, ALLOW FILTERING is redundant. When you filter by partition key and (optionally) clustering column(s), the coordinator will know which replicas store the data, and each of the contacted replicas will infer through local indexing where the data is stored at the storage level, making it an efficient Cassandra read query.

In opposition, when the ALLOW FILTERING query doesn't filter by all terms of the partition key, and/or filters by non-primary key columns, local replica indexing and/or global indexing will be rendered useless, making it so that a large set of replicas scan their full dataset and filter after reading - thus inefficient.

The purpose of the ALLOW FILTERING option is to exceptionally allow queries that require a full scan, since this type of query fails by default, as a safeguard, due to the impact risks.

Answer 2

Some basics: One should never use ALLOW FILTERING in your read queries as that is a bad practice because of the following reasons,

1. This table data model is not designed appropriately based on the application access pattern
2. AF will perform a scan of the entire distributed table

Having said that, it may be okay here in this scenario as you're already providing the full primary keys [partition keys + clustering keys] of the table here and it'd be a no-op for AF.