Skip to content

Aggregates: aggregate-on-join rejects Semi/Anti joins (IN / NOT IN) #4911

Closed

Description

@mithuncy

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)

Activity

  1. added
    bugSomething isn't working
    analyticsAggregates, faceting, and analytics features
    joinsPR or issue related to JOINs
    on Apr 28, 2026
  2. removed their assignment
    on Apr 28, 2026
  3. stuhood commented on Apr 28, 2026

    @stuhood
  4. moved this from Blocked to Todo in ParadeDB Roadmap | Q2 2026on Apr 29, 2026
  5. stuhood commented on Apr 30, 2026

    @stuhood
    Collaborator

    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.

  6. moved this from Todo to In progress in ParadeDB Roadmap | Q2 2026on May 4, 2026
  7. 6 remaining items

  8. philippemnoel commented on May 13, 2026

    @philippemnoel
    Member

    closed in #5005

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

analyticsAggregates, faceting, and analytics featuresbugSomething isn't workingjoinsPR or issue related to JOINspriority-highHigh priority issue

Type

No type

Projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions