Postgres can ORDER 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.
An ORDER 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.
-- Pushdown may not occur over very small tables-- This forces pushdownSET enable_indexscan = off;EXPLAIN SELECT descriptionFROM mock_itemsWHERE description @@@ 'shoes'ORDER BY rating DESCLIMIT 5;
When using ORDER 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:
SELECT description, sale_date, amount, paradedb.score(id) as scoreFROM salesWHERE description @@@ 'laptop'ORDER BY score, sale_date, amountLIMIT 10;
You can verify if partial ORDER BY pushdown occurred by running 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
This feature significantly improves performance when sorting by multiple columns, as the index is used for the first level of sorting, requiring PostgreSQL to perform less work to produce the final ordered results.
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.