---
title: Top K
description: ParadeDB is optimized for quickly finding the Top K results in a table
canonical: https://www.paradedb.com/docs/reference/full-text/top-k
---
This section covers sorting by a field. To sort by BM25 relevance score, see
[BM25 Scoring](/reference/full-text/score).
ParadeDB is highly optimized for quickly returning the Top K results out of the index. In SQL, this means queries that contain an `ORDER BY...LIMIT`:
```sql SQL
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;
```
```ts Drizzle
import { search } from "@paradedb/drizzle-paradedb";
await db
.select({
description: mockItems.description,
rating: mockItems.rating,
category: mockItems.category,
})
.from(mockItems)
.where(search.matchAny(mockItems.description, "running shoes"))
.orderBy(mockItems.rating)
.limit(5);
```
```python Django
from paradedb import MatchAny, ParadeDB
MockItem.objects.filter(
description=ParadeDB(MatchAny('running shoes'))
).order_by('rating').values('description', 'rating', 'category')[:5]
```
```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search
stmt = (
select(MockItem.description, MockItem.rating, MockItem.category)
.where(search.match_any(MockItem.description, "running shoes"))
.order_by(MockItem.rating)
.limit(5)
)
with Session(engine) as session:
session.execute(stmt).all()
```
```ruby Rails
MockItem.search(:description)
.match_any("running shoes")
.order(:rating)
.select(:description, :rating, :category)
.limit(5)
```
```cs EF Core
await dbContext
.MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running shoes"))
.OrderBy(item => item.Rating)
.Select(item => new { item.Description, item.Rating, item.Category })
.Take(5)
.ToListAsync();
```
In order for a Top K query to be executed by ParadeDB vs. vanilla Postgres, all of the following conditions must be met:
1. All `ORDER BY` fields must be indexed. If they are text fields, they [must use the literal tokenizer](#sorting-by-text).
2. For single-table Top K scans, at least one ParadeDB text search operator must be present at the same level as the `ORDER BY...LIMIT` (`IN` and `EXISTS` subqueries across ParadeDB-indexed tables use [join pushdown](/reference/joins/overview#semi-join) instead; see [searching in subqueries](#searching-in-subqueries)).
3. The query must have a `LIMIT`.
4. With the exception of `lower`, ordering by expressions is not supported -- only the raw fields themselves. See [sorting by computed values](#sorting-by-computed-values) for workarounds.
5. Text `ORDER BY` fields must use a byte-ordered [collation](#collation), such as `C`.
To verify that ParadeDB is executing the Top K, look for a `Custom Scan` with a `TopKScanExecState` in the `EXPLAIN` output:
```sql
EXPLAIN SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;
```
```csv
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=10.00..10.02 rows=3 width=552)
-> Custom Scan (ParadeDB Base Scan) on mock_items (cost=10.00..10.02 rows=3 width=552)
Table: mock_items
Index: search_idx
Segment Count: 1
Exec Method: TopKScanExecState
Scores: false
TopK Order By: rating asc
TopK Limit: 5
Tantivy Query: {"with_index":{"query":{"match":{"field":"description","value":"running shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(10 rows)
```
If any of the above conditions are not met, the query cannot be fully optimized and you will not see a `TopKScanExecState` in the `EXPLAIN` output.
## Tiebreaker Sorting
To guarantee stable sorting in the event of a tie, additional columns can be provided to `ORDER BY`.
This is particularly important when sorting by `pdb.score(...)`, as equal scores can result in non-deterministic orderings. As noted above, all tiebreaker columns must be indexed to receive the Top K optimization.
A text tiebreaker, such as a string ID, must also use a byte-ordered [collation](#collation). Prefer an integer tiebreaker where one is available.
```sql SQL
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating, id
LIMIT 5;
```
```ts Drizzle
import { search } from "@paradedb/drizzle-paradedb";
await db
.select({
description: mockItems.description,
rating: mockItems.rating,
category: mockItems.category,
})
.from(mockItems)
.where(search.matchAny(mockItems.description, "running shoes"))
.orderBy(mockItems.rating, mockItems.id)
.limit(5);
```
```python Django
from paradedb import MatchAny, ParadeDB
MockItem.objects.filter(
description=ParadeDB(MatchAny('running shoes'))
).order_by('rating', 'id').values('description', 'rating', 'category')[:5]
```
```python SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search
stmt = (
select(MockItem.description, MockItem.rating, MockItem.category)
.where(search.match_any(MockItem.description, "running shoes"))
.order_by(MockItem.rating, MockItem.id)
.limit(5)
)
with Session(engine) as session:
session.execute(stmt).all()
```
```ruby Rails
MockItem.search(:description)
.match_any("running shoes")
.order(:rating, :id)
.select(:description, :rating, :category)
.limit(5)
```
```cs EF Core
await dbContext
.MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running shoes"))
.OrderBy(item => item.Rating)
.ThenBy(item => item.Id)
.Select(item => new { item.Description, item.Rating, item.Category })
.Take(5)
.ToListAsync();
```
ParadeDB is currently able to handle 3 `ORDER BY` columns. If there are more
than 3 columns, the `ORDER BY` will not be efficiently executed by ParadeDB.
## Sorting by Text
If a text field is present in the `ORDER BY` clause, it must be indexed with the [literal](/reference/tokenizers/available-tokenizers/literal) or
[literal normalized](/reference/tokenizers/available-tokenizers/literal-normalized) tokenizer.
Sorting by lowercase text using `lower()` is also supported. To enable this, the expression `lower()` must be indexed
with either the literal or literal normalized tokenizer. See [indexing expressions](/reference/indexing/indexing-expressions) for more information.
Text sorts also depend on the column's [collation](#collation).
```sql
CREATE INDEX search_idx ON mock_items
USING paradedb (id, (lower(description)::pdb.literal));
```
This allows sorting by lowercase to be optimized.
```sql SQL
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'sleek running shoes'
ORDER BY lower(description)
LIMIT 5;
```
```ts Drizzle
import { sql } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";
await db
.select({
description: mockItems.description,
rating: mockItems.rating,
category: mockItems.category,
})
.from(mockItems)
.where(search.matchAny(mockItems.description, "sleek running shoes"))
.orderBy(sql`lower(${mockItems.description})`)
.limit(5);
```
```python Django
from django.db.models.functions import Lower
from paradedb import MatchAny, ParadeDB
MockItem.objects.filter(
description=ParadeDB(MatchAny('sleek running shoes'))
).order_by(Lower('description')).values('description', 'rating', 'category')[:5]
```
```python SQLAlchemy
from sqlalchemy import func, select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search
stmt = (
select(MockItem.description, MockItem.rating, MockItem.category)
.where(search.match_any(MockItem.description, "sleek running shoes"))
.order_by(func.lower(MockItem.description))
.limit(5)
)
with Session(engine) as session:
session.execute(stmt).all()
```
```ruby Rails
description = MockItem.arel_table[:description]
lower_description = Arel::Nodes::NamedFunction.new("LOWER", [description])
MockItem.search(:description)
.match_any("sleek running shoes")
.order(Arel::Nodes::Ascending.new(lower_description))
.select(:description, :rating, :category)
.limit(5)
```
```cs EF Core
await dbContext
.MockItems.Where(item =>
EF.Functions.MatchAny(item.Description, "sleek running shoes")
)
.OrderBy(item => item.Description.ToLower())
.Select(item => new { item.Description, item.Rating, item.Category })
.Take(5)
.ToListAsync();
```
## Sorting by Range
Postgres range columns (`int4range`, `int8range`, `numrange`, `daterange`, `tsrange`, `tstzrange`) can receive the Top K optimization when they appear as the **leading** `ORDER BY` key. ParadeDB compares range values using the same rules as Postgres `range_cmp` (empty ranges, unbounded bounds, and inclusive/exclusive endpoints).
Range columns are always available to ParadeDB's columnar execution path, so no extra configuration is needed. If a range column appears in a later `ORDER BY` position, the whole `ORDER BY` falls back to a regular Postgres sort. Tiebreaker columns after a leading range key are fine.
```sql
SELECT description, rating, weight_range
FROM mock_items
WHERE description @@@ 'shoes'
ORDER BY weight_range
LIMIT 10;
```
## Sorting by JSON
Ordering by a JSON subfield directly is not yet supported. For example, this query will not receive an optimized Top K scan:
```sql
SELECT id, description, metadata
FROM mock_items
WHERE description ||| 'sleek running shoes'
ORDER BY metadata->'weight'
LIMIT 5;
```
However, you can achieve an optimized Top K scan by projecting the JSON field, casting it to the desired type, and indexing it with `pdb.alias`.
For example, to order by a numeric `weight` field within a JSON column:
```sql
CREATE INDEX json_topk_idx ON mock_items
USING paradedb (id, description, (((metadata->>'weight')::int)::pdb.alias('weight')));
```
You can then query and order by the exact underlying expression, which will now use the Top K optimization:
```sql
SELECT id, description, (metadata->>'weight')::int AS weight
FROM mock_items
WHERE description ||| 'sleek running shoes'
ORDER BY (metadata->>'weight')::int
LIMIT 5;
```
## Collation
ParadeDB sorts text by its raw bytes, so a text `ORDER BY` field only receives the Top K optimization if its collation also sorts by bytes.
The `C` and `POSIX` collations, their `.UTF-8` variants, and the `builtin` provider on Postgres 17+ qualify.
ICU collations and libc locales such as `en_US.UTF-8` do not.
If your database's default collation is not byte-ordered, add `COLLATE "C"` to text `ORDER BY` fields:
```sql
EXPLAIN (COSTS OFF)
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY lower(description) COLLATE "C"
LIMIT 5;
```
```csv
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
-> Custom Scan (ParadeDB Base Scan) on mock_items
Table: mock_items
Index: search_idx
Exec Method: TopKScanExecState
Scores: false
TopK Order By: literal_description asc
TopK Limit: 5
Tantivy Query: {"with_index":{"query":{"match":{"field":"description","value":"running shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(9 rows)
```
With a collation that is not byte-ordered, Postgres sorts the results itself instead, and ParadeDB emits a warning:
```sql
EXPLAIN (COSTS OFF)
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY lower(description) COLLATE "und-x-icu"
LIMIT 5;
```
```csv
WARNING: Query has LIMIT 5 but is not using Top K scan (using Normal instead). Reason: ORDER BY columns whose collation is not byte-ordered (C-like) cannot be pushed down to the index. This may cause poor performance on large datasets. Remedies: Specify COLLATE "C" in your query, or use a byte-ordered collation instead. To disable this warning: SET paradedb.planner_warnings = 'off' (table: mock_items)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
-> Sort
Sort Key: ((lower(description))::text) COLLATE "und-x-icu"
-> Custom Scan (ParadeDB Base Scan) on mock_items
Table: mock_items
Index: search_idx
Exec Method: NormalScanExecState
Scores: false
Tantivy Query: {"with_index":{"query":{"match":{"field":"description","value":"running shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(9 rows)
```
`COLLATE "C"` can change the order of results compared to a linguistic
collation. For example, uppercase letters sort before lowercase letters.
## Sorting by Computed Values
Because ordering by expressions is not supported, `ORDER BY` clauses built on `CASE` do not receive the Top K optimization. For example, this query lists in-stock items first:
```sql
SELECT id, description, rating, in_stock
FROM mock_items
WHERE description ||| 'shoes'
ORDER BY CASE WHEN in_stock THEN 0 ELSE 1 END, rating DESC, id
LIMIT 5;
```
There are two ways to keep the Top K optimization:
1. Store the computed value in its own column, add that column to the index, and sort by it.
2. If the expression has only a few possible values, run one Top K query per value and merge the results.
The second approach looks like this:
```sql
EXPLAIN (COSTS OFF)
SELECT * FROM (
(SELECT id, description, rating, in_stock
FROM mock_items
WHERE description ||| 'shoes' AND in_stock = true
ORDER BY rating DESC, id
LIMIT 5)
UNION ALL
(SELECT id, description, rating, in_stock
FROM mock_items
WHERE description ||| 'shoes' AND in_stock = false
ORDER BY rating DESC, id
LIMIT 5)
) t
ORDER BY in_stock DESC, rating DESC, id
LIMIT 5;
```
```csv
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
-> Sort
Sort Key: mock_items.in_stock DESC, mock_items.rating DESC, mock_items.id
-> Append
-> Limit
-> Custom Scan (ParadeDB Base Scan) on mock_items
Table: mock_items
Index: search_idx
Exec Method: TopKScanExecState
Scores: false
TopK Order By: rating desc, id asc
TopK Limit: 5
Tantivy Query: {"boolean":{"must":[{"with_index":{"query":{"match":{"field":"description","value":"shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}},{"term":{"field":"in_stock","value":true}}]}}
-> Limit
-> Custom Scan (ParadeDB Base Scan) on mock_items mock_items_1
Table: mock_items
Index: search_idx
Exec Method: TopKScanExecState
Scores: false
TopK Order By: rating desc, id asc
TopK Limit: 5
Tantivy Query: {"boolean":{"must":[{"with_index":{"query":{"match":{"field":"description","value":"shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}},{"term":{"field":"in_stock","value":false}}]}}
(22 rows)
```
Each branch is a Top K query, so the outer sort only sees at most 10 rows.
This returns the same rows as the `CASE` version as long as every row falls into exactly one branch.
If `in_stock` can be `NULL`, add a third branch for `in_stock IS NULL`.
## Searching in Subqueries
Single-table Top K scans require the search, `ORDER BY`, and `LIMIT` to be at the same level of the query (whereas `IN` and `EXISTS` subqueries across ParadeDB-indexed tables can still be accelerated as [semi joins](/reference/joins/overview#semi-join)).
A common way to miss this on one table is to run one search per field in a `UNION ALL` subquery and sort the combined results outside of it:
```sql
EXPLAIN (COSTS OFF)
SELECT id, description, rating
FROM mock_items
WHERE id IN (
SELECT id FROM mock_items WHERE description ||| 'shoes'
UNION ALL
SELECT id FROM mock_items WHERE category ||| 'footwear'
)
ORDER BY rating DESC, id
LIMIT 5;
```
```csv
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
-> Sort
Sort Key: mock_items.rating DESC, mock_items.id
-> Hash Semi Join
Hash Cond: (mock_items.id = mock_items_1.id)
-> Seq Scan on mock_items
-> Hash
-> Append
-> Custom Scan (ParadeDB Base Scan) on mock_items mock_items_1
Table: mock_items
Index: search_idx
Exec Method: ColumnarExecState
Columnar: id
Scores: false
Tantivy Query: {"with_index":{"query":{"match":{"field":"description","value":"shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
-> Custom Scan (ParadeDB Base Scan) on mock_items mock_items_2
Table: mock_items
Index: search_idx
Exec Method: ColumnarExecState
Columnar: id
Scores: false
Tantivy Query: {"with_index":{"query":{"match":{"field":"category","value":"footwear","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(22 rows)
```
The searches have no `LIMIT` of their own, so each one returns every matching row, shown as `ColumnarExecState` instead of `TopKScanExecState`.
Postgres then joins all of those rows and sorts them to keep five, so the cost grows with the number of matches rather than with the `LIMIT`.
Instead, combine the fields with `OR` in a single query:
```sql
EXPLAIN (COSTS OFF)
SELECT id, description, rating
FROM mock_items
WHERE description ||| 'shoes' OR category ||| 'footwear'
ORDER BY rating DESC, id
LIMIT 5;
```
```csv
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
-> Custom Scan (ParadeDB Base Scan) on mock_items
Table: mock_items
Index: search_idx
Exec Method: TopKScanExecState
Scores: false
TopK Order By: rating desc, id asc
TopK Limit: 5
Tantivy Query: {"boolean":{"should":[{"with_index":{"query":{"match":{"field":"description","value":"shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}},{"with_index":{"query":{"match":{"field":"category","value":"footwear","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}]}}
(9 rows)
```
### Joining After the Top K
When the query also joins other tables, ParadeDB can often execute the whole join with [join pushdown](/reference/joins/overview).
If join pushdown does not apply, for example because one of the tables has no ParadeDB index, run the Top K first in a CTE or subquery and join only the rows it returns:
```sql
EXPLAIN (COSTS OFF)
WITH top_orders AS (
SELECT order_id, product_id, order_total
FROM orders
WHERE customer_name ||| 'john'
ORDER BY order_total DESC, order_id
LIMIT 5
)
SELECT t.order_id, t.order_total, m.description
FROM top_orders t
JOIN mock_items m ON m.id = t.product_id
ORDER BY t.order_total DESC, t.order_id;
```
```csv
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t.order_total DESC, t.order_id
-> Hash Join
Hash Cond: (m.id = t.product_id)
-> Seq Scan on mock_items m
-> Hash
-> Subquery Scan on t
-> Limit
-> Custom Scan (ParadeDB Base Scan) on orders
Table: orders
Index: orders_idx
Exec Method: TopKScanExecState
Scores: false
TopK Order By: order_total desc, order_id asc
TopK Limit: 5
Tantivy Query: {"with_index":{"query":{"match":{"field":"customer_name","value":"john","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(16 rows)
```
This returns the same rows as applying the `LIMIT` after the join only if the join neither removes nor duplicates search results.
Here, every order matches exactly one product, because `product_id` is `NOT NULL` and has a foreign key to `mock_items`.
If a join can drop rows or return several rows per search result, the limited CTE can return fewer or different rows.
For pagination with `OFFSET n`, the inner `LIMIT` must be `n` plus the page size.