Run Queries

Now that your environment is configured, select the codetab for your tool and run some queries.

Text Search

We’re now ready to execute a basic text search query. We’ll look for matches where description matches running shoes where rating is greater than 2.

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

import { and, gt } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    rating: mockItems.rating,
    category: mockItems.category,
  })
  .from(mockItems)
  .where(
    and(
      search.matchAny(mockItems.description, "running shoes"),
      gt(mockItems.rating, 2),
    ),
  )
  .orderBy(mockItems.rating)
  .limit(5);

from paradedb import MatchAny, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes')),
    rating__gt=2
).values('description', 'rating', 'category').order_by('rating')[: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"), MockItem.rating > 2)
    .order_by(MockItem.rating)
    .limit(5)
)

with Session(engine) as session:
    session.execute(stmt).all()

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

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAny(item.Description, "running shoes") && item.Rating > 2
    )
    .OrderBy(item => item.Rating)
    .Select(item => new { item.Description, item.Rating, item.Category })
    .Take(5)
    .ToListAsync();

Expected Response

     description     | rating | category
---------------------+--------+----------
 White jogging shoes |      3 | Footwear
 Generic shoes       |      4 | Footwear
 Sleek running shoes |      5 | Footwear
(3 rows)

||| is ParadeDB’s custom match disjunction operator, which means “find me all documents containing running OR shoes. If we want all documents containing running AND shoes, we can use ParadeDB’s &&& match conjunction operator.

SELECT description, rating, category
FROM mock_items
WHERE description &&& 'running shoes' AND rating > 2
ORDER BY rating
LIMIT 5;

import { and, gt } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    rating: mockItems.rating,
    category: mockItems.category,
  })
  .from(mockItems)
  .where(
    and(
      search.matchAll(mockItems.description, "running shoes"),
      gt(mockItems.rating, 2),
    ),
  )
  .orderBy(mockItems.rating)
  .limit(5);

from paradedb import MatchAll, ParadeDB

MockItem.objects.filter(
    description=ParadeDB(MatchAll('running shoes')),
    rating__gt=2
).values('description', 'rating', 'category').order_by('rating')[: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_all(MockItem.description, "running shoes"), MockItem.rating > 2)
    .order_by(MockItem.rating)
    .limit(5)
)

with Session(engine) as session:
    session.execute(stmt).all()

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

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAll(item.Description, "running shoes") && item.Rating > 2
    )
    .OrderBy(item => item.Rating)
    .Select(item => new { item.Description, item.Rating, item.Category })
    .Take(5)
    .ToListAsync();

Expected Response

     description     | rating | category
---------------------+--------+----------
 Sleek running shoes |      5 | Footwear
(1 row)

BM25 Scoring

Next, let’s add BM25 scoring to the results, which sorts matches by relevance. To do this, we’ll use pdb.score.

SELECT description, pdb.score(id)
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY score DESC
LIMIT 5;

import { and, desc, gt } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    score: search.score(mockItems.id),
  })
  .from(mockItems)
  .where(
    and(
      search.matchAny(mockItems.description, "running shoes"),
      gt(mockItems.rating, 2),
    ),
  )
  .orderBy(desc(search.score(mockItems.id)))
  .limit(5);

from paradedb import MatchAny, ParadeDB, Score

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes')),
    rating__gt=2
).annotate(
    score=Score()
).values('description', 'score').order_by('-score')[:5]

from sqlalchemy import desc, select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import pdb, search

stmt = (
    select(MockItem.description, pdb.score(MockItem.id).label("score"))
    .where(search.match_any(MockItem.description, "running shoes"), MockItem.rating > 2)
    .order_by(desc("score"))
    .limit(5)
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:description)
        .match_any("running shoes")
        .where(rating: 3..)
        .with_score
        .select(:description)
        .order(search_score: :desc)
        .limit(5)

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAny(item.Description, "running shoes") && item.Rating > 2
    )
    .Select(item => new { item.Description, Score = EF.Functions.Score(item.Id) })
    .OrderByDescending(item => item.Score)
    .Take(5)
    .ToListAsync();

Expected Response

     description     |   score
---------------------+-----------
 Sleek running shoes |  6.817111
 Generic shoes       | 3.8772602
 White jogging shoes | 3.4849067
(3 rows)

Vector Search

Native vector search support for ParadeDB requires version 0.25.0 or above.

The mock_items table also contains an embedding column. Let’s execute the same query above, but order results by vector similarity instead of BM25 score:

SELECT description, embedding
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY embedding <=> '[1, 2, 3, 4, 5, 6, 7, 8]'
LIMIT 5;

import { and, gt } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";

const queryEmbedding = [1, 2, 3, 4, 5, 6, 7, 8];

await db
  .select({
    description: mockItems.description,
    embedding: mockItems.embedding,
  })
  .from(mockItems)
  .where(
    and(
      search.matchAny(mockItems.description, "running shoes"),
      gt(mockItems.rating, 2),
    ),
  )
  .orderBy(search.cosineDistance(mockItems.embedding, queryEmbedding))
  .limit(5);

from paradedb import MatchAny, ParadeDB
from paradedb.vector import CosineDistance

query_embedding = [1, 2, 3, 4, 5, 6, 7, 8]

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes')),
    rating__gt=2
).order_by(
    CosineDistance('embedding', query_embedding)
).values('description', 'embedding')[:5]

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import search, vector

query_embedding = [1, 2, 3, 4, 5, 6, 7, 8]

stmt = (
    select(MockItem.description, MockItem.embedding)
    .where(search.match_any(MockItem.description, "running shoes"), MockItem.rating > 2)
    .order_by(vector.cosine_distance(MockItem.embedding, query_embedding))
    .limit(5)
)

with Session(engine) as session:
    session.execute(stmt).all()

query_embedding = [1, 2, 3, 4, 5, 6, 7, 8]

MockItem.search(:description)
        .match_any("running shoes")
        .where(rating: 3..)
        .nearest(:embedding, query_embedding, metric: :cosine)
        .select(:description, :embedding)
        .limit(5)

var queryEmbedding = new float[] { 1, 2, 3, 4, 5, 6, 7, 8 };

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAny(item.Description, "running shoes") && item.Rating > 2
    )
    .OrderBy(item => EF.Functions.CosineDistance(item.Embedding, queryEmbedding))
    .Select(item => new { item.Description, item.Embedding })
    .Take(5)
    .ToListAsync();

Expected Response

     description     |                                  embedding
---------------------+------------------------------------------------------------------------------
 White jogging shoes | [-0.042016,0.399574,-0.663812,-0.071006,0.430261,0.21037,0.392919,-0.095506]
 Sleek running shoes | [-0.01708,0.467566,-0.757486,0.134082,0.33933,0.04403,0.185394,-0.194613]
 Generic shoes       | [-0.28609,0.368785,-0.810201,0.011777,0.132327,0.199342,0.056281,-0.255287]
(3 rows)

Because embedding is part of the same index as description and rating, the entire query (the full-text match, the rating filter, and the nearest-neighbor ordering) executes inside the ParadeDB index. ParadeDB is the only Postgres index that can do this — every other Postgres vector index is a standalone index that requires separate pre/post filtering, which hurts performance and recall.

Top K Ordering

In addition to BM25 and vector similarity, ParadeDB is also optimized for quickly sorting by an arbitrary field, also known as a Top K query. In SQL, this means queries that contain an ORDER BY <field>...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'))
).values('description', 'rating', 'category').order_by('rating')[: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")
        .select(:description, :rating, :category)
        .order(:rating)
        .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();

Expected Response

     description     | rating | category
---------------------+--------+----------
 White jogging shoes |      3 | Footwear
 Generic shoes       |      4 | Footwear
 Sleek running shoes |      5 | Footwear
(3 rows)

The ordering field, in this case rating, must be indexed inside the ParadeDB index.

Highlighting

Finally, let’s also highlight the relevant portions of the documents that were matched. To do this, we’ll use pdb.snippet.

SELECT description, pdb.snippet(description), pdb.score(id)
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY score DESC
LIMIT 5;

import { and, desc, gt } from "drizzle-orm";
import { search } from "@paradedb/drizzle-paradedb";

await db
  .select({
    description: mockItems.description,
    snippet: search.snippet(mockItems.description),
    score: search.score(mockItems.id),
  })
  .from(mockItems)
  .where(
    and(
      search.matchAny(mockItems.description, "running shoes"),
      gt(mockItems.rating, 2),
    ),
  )
  .orderBy(desc(search.score(mockItems.id)))
  .limit(5);

from paradedb import MatchAny, ParadeDB, Score, Snippet

MockItem.objects.filter(
    description=ParadeDB(MatchAny('running shoes')),
    rating__gt=2
).annotate(
    snippet=Snippet('description'),
    score=Score()
).values('description', 'snippet', 'score').order_by('-score')[:5]

from sqlalchemy import desc, select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import pdb, search

stmt = (
    select(
        MockItem.description,
        pdb.snippet(MockItem.description).label("snippet"),
        pdb.score(MockItem.id).label("score"),
    )
    .where(search.match_any(MockItem.description, "running shoes"), MockItem.rating > 2)
    .order_by(desc("score"))
    .limit(5)
)

with Session(engine) as session:
    session.execute(stmt).all()

MockItem.search(:description)
        .match_any("running shoes")
        .where(rating: 3..)
        .with_snippet(:description)
        .with_score
        .select(:description)
        .order(search_score: :desc)
        .limit(5)

await dbContext
    .MockItems.Where(item =>
        EF.Functions.MatchAny(item.Description, "running shoes") && item.Rating > 2
    )
    .Select(item => new
    {
        item.Description,
        Snippet = EF.Functions.Snippet(item.Description),
        Score = EF.Functions.Score(item.Id)
    })
    .OrderByDescending(item => item.Score)
    .Take(5)
    .ToListAsync();

Expected Response

     description     |              snippet              |   score
---------------------+-----------------------------------+-----------
 Sleek running shoes | Sleek <b>running</b> <b>shoes</b> |  6.817111
 Generic shoes       | Generic <b>shoes</b>              | 3.8772602
 White jogging shoes | White jogging <b>shoes</b>        | 3.4849067
(3 rows)

Faceted queries allow a single query to return both the Top K results and an aggregate value, which is more CPU-efficient than issuing two separate queries. For example, the following query returns the top 3 results as well as the total number of results matched.

SELECT
     description, rating, category,
     pdb.agg('{"value_count": {"field": "id"}}') OVER ()
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,
    agg: search.agg({ value_count: { field: "id" } }).over(),
  })
  .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]
    .facets(agg='{"value_count": {"field": "id"}}')
)

from sqlalchemy import select
from sqlalchemy.orm import Session
from paradedb.sqlalchemy import facets, search

base = (
    select(MockItem.description, MockItem.rating, MockItem.category)
    .where(search.match_any(MockItem.description, "running shoes"))
    .order_by(MockItem.rating)
    .limit(5)
)

stmt = facets.with_rows(base, agg=facets.value_count(field="id"), key_field=MockItem.id)

with Session(engine) as session:
    rows = session.execute(stmt).all()
    facets.extract(rows)

relation = MockItem.search(:description)
                   .match_any("running shoes")
                   .with_agg(agg: ParadeDB::Aggregations.value_count(:id))
                   .order(:rating)
                   .select(:description, :rating, :category)
                   .limit(5)

rows = relation.to_a
facets = relation.aggregates

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,
        Agg = EF.Functions.AggOver(new { value_count = new { field = "id" } })
    })
    .Take(5)
    .ToListAsync();

Expected Response

     description     | rating | category |      agg
---------------------+--------+----------+----------------
 White jogging shoes |      3 | Footwear | {"value": 3.0}
 Generic shoes       |      4 | Footwear | {"value": 3.0}
 Sleek running shoes |      5 | Footwear | {"value": 3.0}
(3 rows)

That’s it! Next, let’s load your data to start running real queries.

Documentation

Text Search

BM25 Scoring

Vector Search

Top K Ordering

Highlighting

Facets

​Text Search

​BM25 Scoring

​Vector Search

​Top K Ordering

​Highlighting

​Facets

Text Search

BM25 Scoring

Vector Search

Top K Ordering

Highlighting

Facets