`COUNT(*)` with `ctid IN (subquery)` fails with `Pre-filter failed: Column 0 not fetched`
What happens?
COUNT(*) over a ctid IN (subquery) on a bm25-indexed table fails under the default GUCs:
ERROR: Pre-filter failed: Column 0 not fetchedThe aggregate scan plans it on DataFusion as a RightSemi hash join on ctid, and the probe side gets dynamic_filters=1. The filter then asks for a column the scan never fetched (pg_search/src/scan/pre_filter.rs:210). It's the same signature as #5003, which was closed on 2026-07-02 for the join scan path.
Four things make it go away, which is what points at the aggregate scan's semi-join on ctid: SET paradedb.enable_aggregate_custom_scan = off, selecting rows instead of COUNT(*), joining on id instead of ctid, and dropping the bm25 index. The subquery doesn't need @@@ for it to fail.
To Reproduce
CREATE EXTENSION IF NOT EXISTS pg_search;
CREATE TABLE pf (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO pf VALUES (1, 'alice'), (2, 'bob'), (3, 'bob');
CREATE INDEX pf_idx ON pf USING paradedb (id, name)
WITH (text_fields = '{"name": {"tokenizer": {"type": "keyword"}, "fast": true}}');
SELECT count(*) FROM pf WHERE ctid IN (SELECT ctid FROM pf WHERE name @@@ 'bob');
-- ERROR: Pre-filter failed: Column 0 not fetchedThe plan:
Custom Scan (ParadeDB Aggregate Scan)
Backend: DataFusion
Aggregates: COUNT(*)
DataFusion Physical Plan:
: AggregateExec: mode=Single, gby=[], aggr=[count(1) as agg_0]
: HashJoinExec: mode=CollectLeft, join_type=RightSemi, on=[(ctid_1@0, ctid_0@0)], projection=[]
: ProjectionExec: expr=[ctid@0 as ctid_1]
: PgSearchScan: table=pf, segments=1, visibility=eager, query={"term":{"field":"name","value":"bob"}}
: ProjectionExec: expr=[ctid@0 as ctid_0]
: PgSearchScan: table=pf, segments=1, dynamic_filters=1, visibility=eager, query="all"No churn and no vacuum needed. Seen on a PG 15 debug build of main plus #6378, #6380, #6381, #6382 and #6383.
Source: paradedb/paradedb