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