Top K - ParadeDB

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.
The query must have a LIMIT.
With the exception of lower, ordering by expressions is not supported — only the raw fields themselves.

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.

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.

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

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 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')))
WITH (key_field='id', sort_by='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;

​Tiebreaker Sorting

​Sorting by Text

​Sorting by JSON

Tiebreaker Sorting

Sorting by Text

Sorting by JSON