Filtering is a feature that allows you to filter by column that is not part of the primary key without creating an index or MV and without any storage overhead. Filtering can result in really low query performance because it involves a full table-scan. Still, filtering can be really useful if you don’t have high performance requirements for certain queries or if the result of the query returns most of the rows from the table.
Transcript
Filtering is a feature that allows you to filter by column that is not part of the primary key without creating an index or MV and without any storage overhead. Filtering can result in really low query performance because it involves a full table-scan. Still, filtering can be really useful if you don’t have high performance requirements for certain queries or if the result of the query returns most of the rows from the table.
Usage
Create table:
CREATE TABLE buildings (
name TEXT,
city TEXT,
height INT,
PRIMARY KEY (name)
);
Query:
SELECT * FROM buildings WHERE city = ‘Brussels’ ALLOW FILTERING;
Notice the keyword ALLOW FILTERING at the end of the query. If you don’t add that keyword to your query, you see the following error:
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.
When can you use filtering?
Filtering by a non-PK column – without indexing or MV
Filtering by the PK and another column that’s not CK
Using something other than EQ or IN relation (e.g. >=) on the PK
