WHERE
clauses and operators.
For instance, the following query filters out results that do not meet rating > 2
.
Filter Pushdown
Non-Text Fields
While not required, filtering performance over non-text columns can be improved by including them in the BM25 index, and making sure they are configured as fast (which is already the default for non-text fields). When these columns are part of the index,WHERE
clauses that reference them can be pushed down into the index scan itself. This can result in faster query execution over large datasets.
For example, if rating
and created_at
are frequently used in filters, they can be added to the BM25 index during index creation:
Operator | Left Operand Type | Right Operand Type | Example |
---|---|---|---|
= , < , > , <= , >= , <> , BETWEEN | int2 | int2 | WHERE rating = 2 |
int4 | int4 | ||
int8 | int8 | ||
int2 | int4 | ||
int2 | int8 | ||
int4 | int8 | ||
float4 | float4 | ||
float8 | float8 | ||
float4 | float8 | ||
date | date | ||
time | time | ||
timetz | timetz | ||
timestamp | timestamp | ||
timestamptz | timestamptz | ||
uuid | uuid | ||
= | bool | bool | WHERE in_stock = true |
IN , ANY , ALL | bool | bool[] | WHERE rating IN (1,2,3) |
int2 | int2[] | ||
int4 | int4[] | ||
int8 | int8[] | ||
int2 | int4[] | ||
int2 | int8[] | ||
int4 | int8[] | ||
float4 | float4[] | ||
float8 | float8[] | ||
float4 | float8[] | ||
date | date[] | ||
timetz | timetz[] | ||
timestamp | timestamp[] | ||
timestamptz | timestamptz[] | ||
uuid | uuid[] | ||
IS , IS NOT | bool | bool | WHERE in_stock IS true |
IS NULL , IS NOT NULL | bool | WHERE rating IS NOT NULL | |
int2 | |||
int4 | |||
int8 | |||
int2 | |||
int2 | |||
int4 | |||
float4 | |||
float8 | |||
float4 | |||
date | |||
time | |||
timetz | |||
timestamp | |||
timestamptz | |||
uuid |
Text Fields
Suppose we have a text filter that looks for an exact string match likecategory = 'Footwear'
:
category = 'Footwear'
filter, category
must be indexed using the keyword
tokenizer:
keyword
tokenizer: