> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Sorting

<Danger>
  **Legacy Docs:** This page describes our legacy API. It will be deprecated in a future version. Please use the [v2 API](/) where possible.
</Danger>

## Order by Relevance

The `score` column returned by [`pdb.score`](/legacy/full-text/scoring) can be used to sort results by
BM25 relevance.

```sql theme={null}
SELECT description, rating, category, pdb.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`.

```sql theme={null}
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.

```sql theme={null}
SELECT description, rating, category, pdb.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](/legacy/indexing/create-index#text-fields), [numeric](/legacy/indexing/create-index#numeric-fields),
[datetime](/legacy/indexing/create-index#datetime-fields), or [boolean](/legacy/indexing/create-index#boolean-fields) field is automatically "pushed down"
to the BM25 index if the `ORDER BY` field is indexed as [fast](/legacy/indexing/fast-fields). 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.

```sql theme={null}
-- 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;
```

<Accordion title="Expected Response">
  ```csv theme={null}
                                                                                QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=10.00..34.03 rows=5 width=36)
     ->  Custom Scan (ParadeDB Base 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
              TopK Limit: 5
           Tantivy Query: {"WithIndex":{"query":{"ParseWithField":{"field":"description","query_string":"shoes","lenient":null,"conjunction_mode":null}}}}
  (9 rows)
  ```
</Accordion>

### Ordering by Text Field

If a fast text field is indexed with the `raw` [normalizer](/legacy/indexing/fast-fields#normalizers), `ORDER BY <text_field> LIMIT` can be pushed down.

If the `lowercase` [normalizer](/legacy/indexing/fast-fields#normalizers) is used, then `ORDER BY lower(<text_field>) LIMIT` (but not `ORDER BY <text_field> LIMIT`)
can be pushed down.

```sql theme={null}
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;
```

<Accordion title="Expected Response">
  ```csv theme={null}
                                                             QUERY PLAN
  ---------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=10.00..34.05 rows=5 width=584)
     ->  Custom Scan (ParadeDB Base 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
              TopK Limit: 5
           Tantivy Query: {"ParseWithField":{"field":"description","query_string":"shoes","lenient":null,"conjunction_mode":null}}
  (9 rows)
  ```
</Accordion>

<Note>
  Not all `ORDER BY`s are pushed down. The following queries are not pushed down:

  1. `ORDER BY` without a `LIMIT`.
  2. `ORDER BY` for [indexed expressions](https://www.postgresql.org/docs/current/indexes-expressional.html).
</Note>

## Partial Ordering with Multiple Sort Fields

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:

```sql theme={null}
SELECT description, sale_date, amount, pdb.score(id) as score
FROM sales
WHERE description @@@ 'laptop'
ORDER BY score, sale_date, amount
LIMIT 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 Base 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

<Accordion title="Example Query Plan">
  ```csv theme={null}
                                                                                QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=0.19..0.34 rows=10 width=44) (actual time=1.113..1.114 rows=0 loops=1)
     ->  Incremental Sort  (cost=0.19..0.34 rows=10 width=44) (actual time=1.113..1.114 rows=0 loops=1)
           Sort Key: (pdb.score(id)), sale_date, amount
           Presorted Key: (pdb.score(id))
           Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
           ->  Custom Scan (ParadeDB Base Scan) on sales  (cost=0.00..0.03 rows=10 width=44) (actual time=1.055..1.055 rows=0 loops=1)
                 Table: sales
                 Index: sales_index
                 Segment Count: 1
                 Heap Fetches: 0
                 Exec Method: TopKScanExecState
                 Scores: true
                    Sort Field: pdb.score()
                    Sort Direction: asc
                    TopK Limit: 10
                 Tantivy Query: {"with_index":{"query":{"parse_with_field":{"field":"description","query_string":"keyboard","lenient":null,"conjunction_mode":null}}}}
   Planning Time: 1.488 ms
   Execution Time: 1.651 ms
  (18 rows)
  ```
</Accordion>

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.

<Note>
  Limitations for partial `ORDER BY` pushdown:

  1. Only the first sort field is pushed down to the BM25 index.
  2. The first sort field must be indexed as a fast field.
  3. A `LIMIT` clause is still required.
</Note>