Top K

This section covers sorting by a field. To sort by BM25 relevance score, see BM25 Scoring.

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:

SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;

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);

from paradedb import MatchAny, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes'))
).order_by('rating').values('description', 'rating', 'category')[:5]

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()

MockItem.search(:description)
        .match_any("running shoes")
        .order(:rating)
        .select(:description, :rating, :category)
        .limit(5)

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:

All ORDER BY fields must be indexed. If they are text fields, they must use the literal tokenizer.
At least one ParadeDB text search operator must be present at the same level as the ORDER BY...LIMIT. A search inside a subquery such as IN (...) does not count. See searching in subqueries.
The query must have a LIMIT.
With the exception of lower, ordering by expressions is not supported — only the raw fields themselves. See sorting by computed values for workarounds.
Text ORDER BY fields must use a byte-ordered 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:

EXPLAIN SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;

Expected Response

                                                                                                   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. Prefer an integer tiebreaker where one is available.

SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating, id
LIMIT 5;

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);

from paradedb import MatchAny, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes'))
).order_by('rating', 'id').values('description', 'rating', 'category')[:5]

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()

MockItem.search(:description)
        .match_any("running shoes")
        .order(:rating, :id)
        .select(:description, :rating, :category)
        .limit(5)

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 or literal normalized tokenizer. Sorting by lowercase text using lower(<text_field>) is also supported. To enable this, the expression lower(<text_field>) must be indexed with either the literal or literal normalized tokenizer. See indexing expressions for more information. Text sorts also depend on the column’s collation.

CREATE INDEX search_idx ON mock_items
USING paradedb (id, (lower(description)::pdb.literal));

This allows sorting by lowercase to be optimized.

SELECT description, rating, category
FROM mock_items
WHERE description ||| 'sleek running shoes'
ORDER BY lower(description)
LIMIT 5;

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);

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]

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()

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)

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.

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:

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:

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:

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:

EXPLAIN (COSTS OFF)
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY lower(description) COLLATE "C"
LIMIT 5;

Expected Response

                                                                                                   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:

EXPLAIN (COSTS OFF)
SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY lower(description) COLLATE "und-x-icu"
LIMIT 5;

Expected Response

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:

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:

Store the computed value in its own column, add that column to the index, and sort by it.
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:

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;

Expected Response

                                                                                                                                          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

Top K requires the search, ORDER BY, and LIMIT to be at the same level of the query. A common way to miss this is to run one search per field in a subquery and sort the combined results outside of it:

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;

Expected Response

                                                                                                           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:

EXPLAIN (COSTS OFF)
SELECT id, description, rating
FROM mock_items
WHERE description ||| 'shoes' OR category ||| 'footwear'
ORDER BY rating DESC, id
LIMIT 5;

Expected Response

                                                                                                                                                                                                    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. 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:

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;

Expected Response

                                                                                                            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.

Indexing

Full-Text / Lexical Search

Vector / Semantic Search

Hybrid Search

Aggregations

JOINs

Sharding

Cheatsheets

Tiebreaker Sorting

Sorting by Text

Sorting by Range

Sorting by JSON

Collation

Sorting by Computed Values

Searching in Subqueries

Joining After the Top K

​Tiebreaker Sorting

​Sorting by Text

​Sorting by Range

​Sorting by JSON

​Collation

​Sorting by Computed Values

​Searching in Subqueries

​Joining After the Top K

Tiebreaker Sorting

Sorting by Text

Sorting by Range

Sorting by JSON

Collation

Sorting by Computed Values

Searching in Subqueries

Joining After the Top K