WHERE
clauses and operators.
For instance, the following query filters out results that do not meet rating > 2
.
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 |
category = 'Footwear'
:
category = 'Footwear'
filter, category
must be indexed using the keyword
tokenizer:
keyword
tokenizer: