--- title: Supported Join Types description: JOIN types and pushdown requirements supported by ParadeDB canonical: https://www.paradedb.com/docs/reference/joins/overview --- ParadeDB supports all standard Postgres `JOIN` types, including: - `INNER JOIN` - `LEFT JOIN` - `RIGHT JOIN` - `FULL JOIN` - `SEMI JOIN` - `ANTI JOIN` - `CROSS JOIN` ParadeDB optimizes `JOIN` queries involving search operations through **join pushdown**, focusing on row-reducing ("above-the-fold") workloads such as Top K result sets and [aggregates](/reference/aggregates/limitations#join-support). When join pushdown applies, ParadeDB evaluates search predicates, joins, sorting, limits, or aggregations on index data before touching the Postgres heap. If a query does not meet the pushdown requirements, ParadeDB falls back to Postgres'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 fetching surviving table rows. This page focuses on row-returning Top K joins; for `GROUP BY`, SQL aggregates, and `pdb.agg` across joined tables (which do not require a `LIMIT`), see [Aggregates over Joins](/reference/aggregates/limitations#join-support). For guidance on when to join normalized tables versus denormalizing, see [Schema Modeling](/reference/indexing/schema-modeling). 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 the following conditions (including queries with non-equi join conditions, `SELECT DISTINCT`, `LATERAL unnest(...)` on indexed array columns, or [`pdb.agg(...) OVER ()`](/reference/aggregates/facets) window facets). If any check fails, Postgres executes 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 `&&&`, `===`, or a vector distance `ORDER BY` (`<->`, `<=>`, `<#>`). | | Indexed fields | All join conditions, filters, `SELECT DISTINCT`, and `ORDER BY` columns must be present in the ParadeDB index. Text and JSON fields must be [columnar](/reference/indexing/columnar). | | Ordering | Optional `ORDER BY` on indexed columns or [expressions](/reference/indexing/indexing-expressions), [`pdb.score(...)`](/reference/full-text/score#joined-scores) (including positive `+` and `*` combinations; see [Score-Ordered Joins](#score-ordered-joins)), or vector distance (`<->`, `<=>`, `<#>`). | | LIMIT clause | Row-returning joins must include a `LIMIT` ([aggregate joins](/reference/aggregates/limitations#join-support) do not require a `LIMIT`). | If any checks fail (including unsupported `ORDER BY` shapes), ParadeDB emits a `WARNING` controlled by `paradedb.planner_warnings` explaining why and falls back to Postgres' native join execution. Vector distance `ORDER BY` currently requires ascending order on `INNER` or `SEMI` joins and excludes rows with a `NULL` vector (matching Base Scan behavior). To demonstrate, let's create a second table called `orders` that can be joined with `mock_items`: ```sql CALL paradedb.create_paradedb_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); ``` ```sql SELECT * FROM orders ORDER BY order_id LIMIT 3; ``` ```csv 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. ```sql 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; ``` ```csv 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. ```csv 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: ```sql 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; ``` ```csv 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. ```csv 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. ```sql 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; ``` ```csv 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. ```csv 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. ```sql 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; ``` ```csv 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. ```csv 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. ```sql 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; ``` ```csv 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. ```csv 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. ```sql 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; ``` ```csv 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. ```csv 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) ``` ## Score-Ordered Joins Join pushdown supports ordering by [`pdb.score`](/reference/full-text/score#joined-scores) from a single table or by a monotonic combination of scores across tables using `+`, `*`, and positive constants: ```sql SELECT o.order_id, o.customer_name, m.description, 2.0 * pdb.score(m.id) + pdb.score(o.order_id) AS score FROM orders o INNER JOIN mock_items m ON o.product_id = m.id WHERE o.customer_name ||| 'Johnson' OR m.description ||| 'running shoes' ORDER BY score DESC, o.order_id LIMIT 5; ``` Each `pdb.score(...)` call computes a BM25 score against that table's index, and `+` sums the per-table scores in the same way a single-table BM25 query sums its clauses (unmatched `OR` branches contribute `0.0`). See [How Schema Shape Affects BM25 Scoring](/reference/indexing/schema-modeling#how-schema-shape-affects-bm25-scoring) for how corpus statistics behave across tables. Inside `ParadeDB Join Scan`, ParadeDB chooses between two execution strategies for score-ordered Top K joins: - `HashJoinExec` + `SortExec`: Used on smaller inputs (below [`paradedb.rank_join_min_rows`](/reference/configuration#query-planning), `10000` estimated matching rows by default) or when scanning and joining in one pass is cheaper, such as two-sided conjunctive (`AND`) joins. - `RankJoinExec`: Pulls top-scoring rows incrementally and probes the other side on demand so the join can stop once the Top K scores are established, without scoring every matching row. `RankJoinExec` applies when all of the following hold: - The join is an `INNER JOIN` (or a `SEMI JOIN` ordered by the outer table's score). - `ORDER BY` sorts descending on `pdb.score(...)` or a positive combination built from `+`, `*`, and positive constants. For cross-table `OR` predicates, the score expression must include a positive additive term for every `OR` branch. - `LIMIT` is `1000` or less. On small test tables such as `orders` and `mock_items`, run `SET paradedb.rank_join_min_rows = 0;` before `EXPLAIN` if you want to inspect a `RankJoinExec` plan directly. When `RankJoinExec` is not eligible, `EXPLAIN` emits a `RankJoinExec not used` diagnostic controlled by `paradedb.planner_warnings`. ## Partitioned Joins (Beta) Partitioned joins are currently a Beta feature under active development. When both tables in an equi-join are indexed using [`partition_by`](/reference/indexing/partition-by) on their join keys, ParadeDB automatically joins matching partition ranges locally on each worker without exchanging data between processes. See the [Partitioning Guide](/reference/indexing/partition-by) for setup and column selection advice. ## Performance If your join query isn't as fast as you'd like, we invite you to [open a GitHub issue](https://github.com/paradedb/paradedb/issues).