Sorting - ParadeDB

Order by Relevance

The score column returned by paradedb.score can be used to sort results by BM25 relevance.

SELECT description, rating, category, paradedb.score(id)
FROM mock_items
WHERE description @@@ 'shoes'
ORDER BY score DESC
LIMIT 5;

Order by Field

The result set can be ordered by any field in ASC or DESC order. By default, Postgres orders by ASC.

SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes'
ORDER BY rating DESC
LIMIT 5;

Tiebreaking

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.

SELECT description, rating, category, paradedb.score(id)
FROM mock_items
WHERE category @@@ 'electronics'
ORDER BY score DESC, rating DESC
LIMIT 5;

Fast Ordering

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 pushdown
SET enable_indexscan = off;

EXPLAIN SELECT description
FROM mock_items
WHERE description @@@ 'shoes'
ORDER BY rating DESC
LIMIT 5;

                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..34.03 rows=5 width=36)
   ->  Custom Scan (ParadeDB Scan) on mock_items  (cost=10.00..34.03 rows=5 width=36)
         Table: mock_items
         Index: search_idx
         Scores: false
            Sort Field: rating
            Sort Direction: desc
            Top N Limit: 5
         Tantivy Query: {"WithIndex":{"oid":2073854,"query":{"ParseWithField":{"field":"description","query_string":"shoes","lenient":null,"conjunction_mode":null}}}}
(9 rows)

Ordering by Text Field

If a fast text field is indexed with the raw 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.

CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, category)
WITH (
    key_field='id',
    text_fields='{
        "category": {"fast": true, "normalizer": "lowercase"}
    }'
);

-- category uses normalizer = lowercase, so lower(category) can be pushed down
EXPLAIN SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'shoes'
ORDER BY lower(category) DESC
LIMIT 5;

                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..34.05 rows=5 width=584)
   ->  Custom Scan (ParadeDB Scan) on mock_items  (cost=10.00..34.05 rows=5 width=584)
         Table: mock_items
         Index: search_idx
         Scores: false
            Sort Field: category
            Sort Direction: desc
            Top N Limit: 5
         Tantivy Query: {"ParseWithField":{"field":"description","query_string":"shoes","lenient":null,"conjunction_mode":null}}
(9 rows)

Not all ORDER BYs are pushed down. The following queries are not pushed down:

ORDER BYs over multiple fields for tiebreaking.
Using paradedb.score with an ORDER BY over another field.
ORDER BY without a LIMIT.

Documentation

​Order by Relevance

​Order by Field

​Tiebreaking

​Fast Ordering

​Ordering by Text Field

Order by Relevance

Order by Field

Tiebreaking

Fast Ordering

Ordering by Text Field