Joins Overview - ParadeDB

ParadeDB supports all standard PostgreSQL JOIN types, including:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
SEMI JOIN
ANTI JOIN

ParadeDB optimizes standard JOIN queries involving search operations through a feature called join pushdown. Our goal is to support and accelerate all JOIN queries shaped like Top-K or aggregate queries. Because join pushdown is enabled by default, ParadeDB automatically executes parts of a JOIN directly inside the ParadeDB executor for queries that meet the requirements. If a query does not meet the requirements, ParadeDB falls back to PostgreSQL’s native row-based execution.

Join Pushdown

Join pushdown reduces query latency by answering as much of the query as possible using the index before touching the underlying tables.

To disable join pushdown for debugging, run SET paradedb.enable_join_custom_scan TO off;.

Requirements for Join Pushdown

Join pushdown is automatically used when a query meets several conditions. If any of these are not satisfied, PostgreSQL will simply execute the join normally.

Requirement	Description
ParadeDB indexes	All tables participating in the join must have a ParadeDB index.
Search predicate	The query must contain a ParadeDB operator such as `&&&`, `===`, etc.
Equi-join key	The join must contain at least one equality condition such as `a.id = b.id`.
Indexed fields	All join keys, filters, and `ORDER BY` columns must be present in the ParadeDB index. Text and JSON fields must be columnar.
LIMIT clause	The query must include a `LIMIT`.

If any checks fail, ParadeDB will emit a NOTICE explaining why and fall back to Postgres’ native join execution. To demonstrate, let’s create a second table called orders that can be joined with mock_items:

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

ALTER TABLE orders
ADD CONSTRAINT foreign_key_product_id
FOREIGN KEY (product_id)
REFERENCES mock_items(id);

CREATE INDEX orders_idx ON orders
USING paradedb (order_id, product_id, order_quantity, order_total, customer_name)
WITH (key_field = 'order_id');

SELECT * FROM orders ORDER BY order_id LIMIT 3;

Expected Response

 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)

Supported Join Types

Inner Join

An inner join returns rows where a matching row exists in both tables according to the join condition.

SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
INNER JOIN mock_items m
  ON o.product_id = m.id
WHERE m.description ||| 'keyboard'
AND o.customer_name ||| 'John'
ORDER BY o.order_total DESC
LIMIT 5;

Expected Response

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

To verify join pushdown, run EXPLAIN on the query and look for a ParadeDB Join Scan in the output.

Expected Response

                                                                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=44)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
         Relation Tree: m INNER o
         Join Cond: o.product_id = m.id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, NULL as col_2, order_total@2 as col_3, NULL as col_4, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[m, o]
           :       HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         CooperativeExec
           :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"keyboard","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=2, query={"with_index":{"query":{"match":{"field":"customer_name","value":"John","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(15 rows)

Semi Join

A semi join returns rows from the left table when a matching row exists in the right table. In SQL, this usually appears as an IN or EXISTS query:

SELECT o.order_id, o.order_total FROM orders o
WHERE o.product_id IN (
  SELECT m.id
  FROM mock_items m
  WHERE m.description ||| 'keyboard'
)
ORDER BY o.order_total DESC
LIMIT 5;

Expected Response

 order_id | order_total
----------+-------------
       27 |      676.15
       57 |      676.15
       11 |      633.94
       41 |      633.94
        4 |      501.87
(5 rows)

To verify join pushdown, run EXPLAIN on the query and look for a ParadeDB Join Scan in the output.

Expected Response

                                                                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=11)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=11)
         Relation Tree: m INNER o
         Join Cond: o.product_id = m.id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, order_total@2 as col_2, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[m, o]
           :       HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         CooperativeExec
           :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"keyboard","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=2, query="all"
(15 rows)

Anti Join

An anti join returns rows from the left table when no matching row exists in the right table. This typically appears as NOT EXISTS or NOT IN.

SELECT o.order_id, o.order_total FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM mock_items m
  WHERE m.id = o.product_id
    AND m.description ||| 'keyboard'
)
ORDER BY o.order_total DESC
LIMIT 5;

Expected Response

 order_id | order_total
----------+-------------
       10 |      638.73
       40 |      638.73
       21 |      632.08
       51 |      632.08
       22 |      605.18
(5 rows)

To verify join pushdown, run EXPLAIN on the query and look for a ParadeDB Join Scan in the output.

Expected Response

                                                                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=11)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=11)
         Relation Tree: o ANTI m
         Join Cond: m.id = o.product_id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, order_total@1 as col_2, ctid_0@0 as ctid_0]
           :   SortExec: TopK(fetch=5), expr=[order_total@1 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[o]
           :       HashJoinExec: mode=CollectLeft, join_type=RightAnti, on=[(id@0, product_id@1)], projection=[ctid_0@0, order_total@2]
           :         CooperativeExec
           :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"keyboard","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=1, query="all"
(15 rows)

Left Join

A left join returns all rows from the left table and matching rows from the right table.

SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
LEFT JOIN mock_items m
  ON o.product_id = m.id
WHERE o.customer_name ||| 'John'
ORDER BY o.order_total DESC
LIMIT 5;

Expected Response

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

To verify join pushdown, run EXPLAIN on the query and look for a ParadeDB Join Scan in the output.

Expected Response

                                                                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=44)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
         Relation Tree: m RIGHT o
         Join Cond: o.product_id = m.id
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, NULL as col_2, order_total@2 as col_3, NULL as col_4, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     VisibilityFilterExec: tables=[o]
           :       HashJoinExec: mode=CollectLeft, join_type=Right, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         VisibilityFilterExec: tables=[m]
           :           CooperativeExec
           :             PgSearchScan: segments=1, query="all"
           :         CooperativeExec
           :           PgSearchScan: segments=1, dynamic_filters=1, 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)

Right Join

A right join returns all rows from the right table and matching rows from the left table.

SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
RIGHT JOIN mock_items m
  ON o.product_id = m.id
WHERE m.description ||| 'book'
ORDER BY o.order_total DESC
LIMIT 5;

Expected Response

 order_id | customer_name | order_total |        description
----------+---------------+-------------+---------------------------
          |               |             | Historical fiction book
       21 | Jada Smith    |      632.08 | Hardcover book on history
       51 | Jada Smith    |      632.08 | Hardcover book on history
        5 | Jane Smith    |      361.38 | Hardcover book on history
(4 rows)

To verify join pushdown, run EXPLAIN on the query and look for a ParadeDB Join Scan in the output.

Expected Response

                                                                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.05 rows=5 width=44)
   ->  Result  (cost=10.00..11.05 rows=5 width=44)
         ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
               Relation Tree: o RIGHT m
               Join Cond: o.product_id = m.id
               Limit: 5
               Order By: o.order_total desc
               DataFusion Physical Plan:
                 : ProjectionExec: expr=[NULL as col_1, NULL as col_2, NULL as col_3, order_total@1 as col_4, ctid_0@0 as ctid_0, ctid_1@2 as ctid_1]
                 :   SortExec: TopK(fetch=5), expr=[order_total@1 DESC], preserve_partitioning=[false]
                 :     VisibilityFilterExec: tables=[m]
                 :       HashJoinExec: mode=CollectLeft, join_type=Right, on=[(product_id@1, id@1)], projection=[ctid_0@0, order_total@2, ctid_1@3]
                 :         VisibilityFilterExec: tables=[o]
                 :           CooperativeExec
                 :             PgSearchScan: segments=1, query="all"
                 :         CooperativeExec
                 :           PgSearchScan: segments=1, query={"with_index":{"query":{"match":{"field":"description","value":"book","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
(17 rows)

Full Join

A full join returns all rows when there is a match in either the left or the right table.

SELECT o.order_id, o.customer_name, o.order_total, m.description
FROM orders o
FULL JOIN mock_items m
  ON o.product_id = m.id
WHERE (m.description ||| 'book' OR o.customer_name ||| 'John')
ORDER BY o.order_total DESC
LIMIT 5;

Expected Response

 order_id | customer_name | order_total |        description
----------+---------------+-------------+---------------------------
          |               |             | Historical fiction book
       51 | Jada Smith    |      632.08 | Hardcover book on history
       21 | Jada Smith    |      632.08 | Hardcover book on history
        4 | John Doe      |      501.87 | Plastic Keyboard
        5 | Jane Smith    |      361.38 | Hardcover book on history
(5 rows)

To verify join pushdown, run EXPLAIN on the query and look for a ParadeDB Join Scan in the output.

Expected Response

                                                                                                                                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.00..11.00 rows=5 width=44)
   ->  Custom Scan (ParadeDB Join Scan)  (cost=10.00..11.00 rows=5 width=44)
         Relation Tree: m FULL o
         Join Cond: o.product_id = m.id
         Join Predicate: (m:{"with_index":{"query":{"match":{"field":"description","value":"book","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}} OR o:{"with_index":{"query":{"match":{"field":"customer_name","value":"John","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}})
         Limit: 5
         Order By: o.order_total desc
         DataFusion Physical Plan:
           : ProjectionExec: expr=[NULL as col_1, NULL as col_2, order_total@2 as col_3, NULL as col_4, ctid_0@0 as ctid_0, ctid_1@1 as ctid_1]
           :   SortExec: TopK(fetch=5), expr=[order_total@2 DESC], preserve_partitioning=[false]
           :     FilterExec: pdb_search_predicate(ctid_0@0) OR pdb_search_predicate(ctid_1@1)
           :       HashJoinExec: mode=CollectLeft, join_type=Full, on=[(id@1, product_id@1)], projection=[ctid_0@0, ctid_1@2, order_total@4]
           :         VisibilityFilterExec: tables=[m]
           :           CooperativeExec
           :             PgSearchScan: segments=1, query="all"
           :         VisibilityFilterExec: tables=[o]
           :           CooperativeExec
           :             PgSearchScan: segments=1, query="all"
(18 rows)

Performance

If your join query isn’t as fast as you’d like, we invite you to open a Github issue.

​Join Pushdown

​Requirements for Join Pushdown

​Supported Join Types

​Inner Join

​Semi Join

​Anti Join

​Left Join

​Right Join

​Full Join

​Performance

Join Pushdown

Requirements for Join Pushdown

Supported Join Types

Inner Join

Semi Join

Anti Join

Left Join

Right Join

Full Join

Performance