Joins

​

Basic Usage

@@@ operates well over JOIN statements. To demonstrate, let’s create a table called orders with a foreign key referencing the example mock_items table.

CALL paradedb.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'orders',
  table_type => 'Orders'
);

SELECT * FROM orders LIMIT 3;

 order_id | product_id | order_quantity | order_total | customer_name
----------+------------+----------------+-------------+---------------
        1 |          1 |              3 |       99.99 | John Doe
        2 |          2 |              1 |       49.99 | Jane Smith
        3 |          3 |              5 |      249.95 | Alice Johnson
(3 rows)

Next, let’s create a BM25 index over the orders table.

CALL paradedb.create_bm25(
  index_name => 'orders_idx',
  table_name => 'orders',
  key_field => 'order_id',
  text_fields => paradedb.field('customer_name')
);

We can now search for orders by customer_name and description:

SELECT order_id, description, customer_name
FROM orders
JOIN mock_items on mock_items.id = orders.product_id
WHERE mock_items.id @@@ 'description:keyboard'
AND orders.order_id @@@ 'customer_name:john';

 order_id |       description        | customer_name
----------+--------------------------+---------------
        1 | Ergonomic metal keyboard | John Doe
        4 | Plastic Keyboard         | John Doe
(2 rows)

​

Join Index

A Postgres B-tree index can significantly improve join performance. By indexing the column(s) used in the join condition, Postgres can quickly locate rows in the “many” side of the join without performing a full table scan.

For example, the following code block creates an index on the column in the orders table used in the join condition.

CREATE INDEX ON orders(product_id);