This guide will walk you through a few queries to give you a feel for ParadeDB.

Create Example Table

ParadeDB comes with a helpful procedure that creates a table populated with mock data to help you get started. Once connected with psql, run the following commands to create and inspect this table.
CALL paradedb.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'mock_items'
);

SELECT description, rating, category
FROM mock_items
LIMIT 3;
Next, let’s create a BM25 index called search_idx on this table. A BM25 index is a covering index, which means that multiple columns can be included in the same index.
CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, category, rating, in_stock, created_at, metadata, weight_range)
WITH (key_field='id');
As a general rule of thumb, any columns that you want to filter, COUNT, GROUP BY, or ORDER BY as part of a full text query should be added to the index for faster performance.
Note the mandatory key_field option. Every BM25 index needs a key_field, which should be the name of a column that will function as a row’s unique identifier within the index. Additionally, the key_field must be the first field in the list of columns. See choosing a key field for more details.

Match Query

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

BM25 Scoring

Next, let’s add BM25 scoring to the results, which allows us to sort matches by relevance. To do this, we’ll use paradedb.score.
SELECT description, paradedb.score(id)
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY score DESC
LIMIT 5;

Highlighting

Finally, let’s also highlight the relevant portions of the documents that were matched. To do this, we’ll use paradedb.snippet.
SELECT description, paradedb.snippet(description), paradedb.score(id)
FROM mock_items
WHERE description ||| 'running shoes' AND rating > 2
ORDER BY score DESC
LIMIT 5;
That’s it! Next, let’s load your data to start running real queries.