Order by Relevance
Thescore
column returned by paradedb.score
can be used to sort results by
BM25 relevance.
Order by Field
The result set can be ordered by any field inASC
or DESC
order. By default, Postgres orders by ASC
.
If
paradedb.score
is present in the query, but the ORDER BY
is not on paradedb.score
, the query will be executed less
efficiently. This is due to the performance overhead of both computing scores and sorting by another field.Tiebreaking
Postgres canORDER BY
multiple columns to break ties in BM25 scores. In the following query, rows with the same
score
will be sorted by rating
in descending order.
Fast Ordering
AnORDER BY...LIMIT
over a single text, numeric,
datetime, or boolean field is automatically “pushed down”
to the BM25 index if the ORDER BY
field is indexed as fast. This makes these queries significantly faster.
You can verify if an ORDER BY...LIMIT
was pushed down by running EXPLAIN
on the query. If pushdown occurred, a Custom Scan
with a
Sort Field
will appear in the query plan.
Expected Response
Expected Response
Ordering by Text Field
If a fast text field is indexed with theraw
normalizer, ORDER BY <text_field> LIMIT
can be pushed down.
If the lowercase
normalizer is used, then ORDER BY lower(<text_field>) LIMIT
(but not ORDER BY <text_field> LIMIT
)
can be pushed down.
Expected Response
Expected Response
Not all
ORDER BY
s are pushed down. The following queries are not pushed down:ORDER BY
without aLIMIT
.ORDER BY
for indexed expressions.
Partial Ordering with Multiple Sort Fields
When usingORDER BY
with multiple sort fields, ParadeDB can partially push down the sorting operation. In this case, only the first column is pushed down to the BM25 index, and PostgreSQL handles the additional columns using sort operations.
For example, in the following query with multiple sort fields, sorting by sale_date
is pushed down to the BM25 index, while sorting by amount
is handled by PostgreSQL:
EXPLAIN
on the query. The query plan will show a Custom Scan
with our ParadeDB scan provider, followed by an appropriate sort operation based on your PostgreSQL version:
- In PostgreSQL 16+: Often uses an
Incremental Sort
node which can take advantage of the already-sorted first column - In older PostgreSQL: Uses a regular
Sort
node, but still benefits from our optimized ordering
Example Query Plan
Example Query Plan
Limitations for partial
ORDER BY
pushdown:- Only the first sort field is pushed down to the BM25 index.
- The first sort field must be indexed as a fast field.
- A
LIMIT
clause is still required.