Repository navigation
Description
What happens?
When a GROUP BY aggregate sits on top of a WHERE x IN (SELECT …) (Postgres-side Semi join) or WHERE x NOT IN (SELECT …) (Anti join), pg_search's Custom Scan (ParadeDB Aggregate Scan) is not selected. The planner falls back to a Postgres-side plan — GroupAggregate over Nested Loop Semi Join over the BM25 base scan — instead of pushing the aggregate into DataFusion.
WARNING: Aggregate Scan (DataFusion) not used: aggregate-on-join does not support Semi JOIN
(table: join)
The gate is in pg_search/src/postgres/customscan/datafusion/translator.rs#L455-L477:
match (pg_jt, allow) {
(PgJoinType::Inner, _) => JoinType::Inner,
(PgJoinType::Left, _) => JoinType::Left,
(PgJoinType::Right, _) => JoinType::Right,
(PgJoinType::Full, _) => JoinType::Full,
(PgJoinType::Semi, JoinTypeAllowList::All) => JoinType::LeftSemi,
(PgJoinType::Anti, JoinTypeAllowList::All) => JoinType::LeftAnti,
...
(jt, JoinTypeAllowList::EquiOnly) => {
return Err(NotImplemented(format!(
"Aggregate-on-join does not support {} JOIN", jt)));
}
...
}The aggregate-on-join translator passes JoinTypeAllowList::EquiOnly, which accepts only {Inner, Left, Right, Full}. The JoinScan path passes JoinTypeAllowList::All, which additionally accepts Semi, Anti, and the right-side variants. Both paths share this same translation function and differ only in the allow-list — so lowering Semi/Anti to DataFusion is already implemented and exercised by the JoinScan path; the aggregate-on-join entry point simply gates it off via EquiOnly.
To Reproduce
CREATE EXTENSION IF NOT EXISTS pg_search;
DROP TABLE IF EXISTS cccf, contact_list CASCADE;
CREATE TABLE cccf (
contact_id bigint PRIMARY KEY,
contact_job_title text
);
CREATE TABLE contact_list (
id serial PRIMARY KEY,
list_id text,
ldf_id bigint
);
INSERT INTO cccf
SELECT s, CASE WHEN s % 20 = 0 THEN 'Senior Programmer' ELSE 'Other' END
FROM generate_series(1, 10000) s;
INSERT INTO contact_list (list_id, ldf_id)
SELECT 'list-A', s FROM generate_series(1, 5000) s;
INSERT INTO contact_list (list_id, ldf_id)
SELECT 'list-B', s FROM generate_series(5001, 10000) s;
CREATE INDEX cccf_idx ON cccf
USING bm25 (contact_id, contact_job_title)
WITH (key_field = contact_id,
text_fields = '{"contact_job_title":{"fast":true,"tokenizer":{"type":"raw"}}}');
CREATE INDEX contact_list_idx ON contact_list
USING bm25 (id, list_id, ldf_id) WITH (key_field = id);
ANALYZE cccf; ANALYZE contact_list;
SET paradedb.enable_aggregate_custom_scan TO on;
-- Repro: warning fires, Aggregate Scan declined, Postgres plan chosen
EXPLAIN ANALYZE
SELECT contact_job_title, COUNT(*) AS doc_count
FROM cccf
WHERE contact_id IN (SELECT ldf_id FROM contact_list WHERE list_id = 'list-A')
AND contact_id NOT IN (SELECT ldf_id FROM contact_list WHERE list_id = 'list-B')
AND contact_id @@@ paradedb.parse('contact_job_title:"Senior Programmer"')
GROUP BY contact_job_title
ORDER BY doc_count DESC
LIMIT 10;
-- Comparison: removing IN / NOT IN lets the Aggregate Scan kick in
EXPLAIN ANALYZE
SELECT contact_job_title, COUNT(*) AS doc_count
FROM cccf
WHERE contact_id @@@ paradedb.parse('contact_job_title:"Senior Programmer"')
GROUP BY contact_job_title
ORDER BY doc_count DESC
LIMIT 10;
-- Cleanup
DROP TABLE cccf, contact_list CASCADE;Plan with IN / NOT IN (Postgres-executed, not pg_search's Aggregate Scan)
Limit
Sort (count(*) DESC)
GroupAggregate ← Postgres operator (NOT pg_search's Aggregate Scan)
Group Key: contact_job_title
Nested Loop Semi Join ← Postgres operator
Parallel Custom Scan (ParadeDB Base Scan) cccf ← only the leaf is pg_search
Filter: NOT (ANY (contact_id = (hashed SubPlan 1).col1))
SubPlan 1
Index Only Scan on contact_list (list_id='list-B')
Index Only Scan on contact_list (list_id='list-A', ldf_id = cccf.contact_id)
Same query without IN / NOT IN (Aggregate Scan kicks in)
Custom Scan (ParadeDB Aggregate Scan) ← single pg_search node
Aggregates: COUNT(*)
So the gate is precisely what the warning says: aggregate-on-join accepts Inner / Left / Right / Full but rejects Semi / Anti.
Environment
- OS: macOS (Darwin 24.5.0, aarch64)
- ParadeDB Version: pg_search 0.23.1 (also reproduced on OSS main HEAD
e6acbbd, 2026-04-28) - ParadeDB distribution: ParadeDB Docker Image (
paradedb/paradedb:latest-pg18)
Metadata
Metadata
Assignees
Labels
Type
Projects
- StatusShow more project fieldsDone
Activity
stuhood commentedon Apr 28, 2026
stuhood commentedon Apr 30, 2026
Discussed offline with @mdashti : we can safely take over any aggregate as long as all of the aggregate inputs are on a preserved side of the join.
fix: accept Semi/Anti joins in agg-on-join (#4911)
fix: correct NOT IN semantics with null-aware LeftAnti
6 remaining items
docs: refresh aggregate_join_semi_anti header to reflect Tests 3-6
refactor: simplify build_null_aware_anti_join — drop dead extra_filte…
refactor: fold null_aware into JoinType::Anti — make invalid states u…
chore: simplify pass on PR-introduced inconsistencies
chore: use let-else at JoinScan wrap_with_semi_anti caller
feat: agg-on-join end-to-end IN/NOT IN/EXISTS/NOT EXISTS with null-aw…
feat: agg-on-join end-to-end IN/NOT IN/EXISTS/NOT EXISTS with null-aw…
feat: agg-on-join end-to-end IN/NOT IN/EXISTS/NOT EXISTS with null-aw…
fix: handle correlated SubPlans + Moe review feedback
feat: agg-on-join end-to-end IN/NOT IN/EXISTS/NOT EXISTS with null-aw…
philippemnoel commentedon May 13, 2026
closed in #5005
test: regress for JoinScan NOT IN null-safety end-to-end
test: JoinScan NOT IN null-safety regress (requires patched datafusio…