[Feature Request]: Index Scan node to consider Vector and Fultlext Index as a whole

### Is there an existing issue for the same feature request?

- [x] I have checked the existing issues.

### Is your feature request related to a problem?

```Markdown

```

### Describe the feature you'd like

[Feature Request]: Index Scan node to consider Vector and Fultlext Index as a whole.

e.g. Plan Reader need to share between indexes. Plugin Hooks can help to reduce the code.

### Describe implementation you've considered

_No response_

### Documentation, Adoption, Use Case, Migration Strategy

# Design Proposal — Unified Index-Search Scan (vector + fulltext, incl. GPU)

Status: proposal · Scope: `pkg/sql/colexec/{vectorscan,apply,table_function}`, `pkg/indexplugin`,
`pkg/vectorindex/*` (ivfflat/hnsw/cagra/ivfpq), `pkg/fulltext*`, `proto/plan.proto` ·
Owner: cpegeric · Base: `main` (post PR #27297 `5735684d69`)

Covers **all six index algorithms as one whole** — vector: ivfflat, hnsw (CPU) + cagra, ivfpq
(GPU) — and fulltext: `fulltext`, `fulltext2` (CPU). Nothing deferred: cagra/ivfpq are designed in
now, gated by the existing GPU build tags; only their *GPU-runtime verification* happens on a GPU box.

---

## 1. Context & motivation

PR #27297 replaced the IVFFLAT `ivf_search` TVF with a first-class planner node
`plan.VectorIndexScan`, executed through a **registry-dispatched, switch-free** path:

```
pkg/sql/colexec/apply/vector_source.go
:99 p, _ := indexplugin.Get(spec.Index.IndexAlgo) // lookup by algo string
:103 s, _ := p.(indexplugin.SearchPlugin) // optional capability
:107 reader, _ := s.Search().NewReader(proc, spec, req) // per-index reader
```

Only IVFFLAT uses it. hnsw, cagra, ivfpq, `fulltext`, `fulltext2` still dispatch through the
per-name `switch` in `table_function.go:144` (`case "hnsw_search" | "cagra_search" |
"ivfpq_search" | "fulltext_search" | "fulltext2_search"`) — the per-algo dispatch the registry
exists to delete. And the shared `VectorIndexScan` proto + `execution.go` bake in IVF-specific
fields (`initial_probe_count`, `first_round_limit`, `bucket_expand_step`, `FirstRoundLimit`,
`PostFilterOverFetch`), so the path is shared in name only.

**Goal:** one execution path for **both families as a whole**, where (1) request-building is a
per-index callback; (2) no algo-specific fields live in the shared proto/Request; (3) generic
plan-expr + reader/relation-scan machinery are shared packages, and every index-specific behavior
is reached **only through plugin Hooks**. (bm25 is a scoring *mode* in fulltext/fulltext2, not an index.)

---

## 2. The unifying model

Same operation for every algorithm: **top-k scan over a hidden index with a query payload,
optional WHERE-prefilter + runtime membership, returning `(pk/doc_id, score, include-cols)`**.

| Concept | ivfflat / hnsw / cagra / ivfpq | `fulltext` / `fulltext2` | Where it lives |
|---|---|---|---|
| Query payload | serialized query vector + elem type | MATCH pattern text (+ mode) | generic `QueryPayload []byte` + `QueryType` |
| Ranking | distance (ASC) | BM25 score (DESC) | generic `Direction` |
| Result window k | `candidate_limit` | pushed `limit` (0 = stream all) | generic `ResultLimit` / `CandidateBudget` |
| WHERE pushdown | `pre_filters` | docfilter bytes | generic `PreFilters` / `MembershipFilter` |
| Runtime filter (RF/BF) | membership | membership | generic `MembershipFilter` |
| Covered columns | `included_columns` | INCLUDE cols | generic `IncludedColumns` |
| Snapshot / identity | yes | yes | generic `Identity` / `ScanSnapshot` |
| Tuning knobs | nprobe / ef_search / itopk / search_width / first_round | k1, b, mode | **per-index** (algo params + algo_exprs) |
| Hidden tables | metadata/centroids/entries; graph; PQ model blob | fulltext storage tables | **per-index** (`HiddenTableRoles`) |
| Search kernel | CPU probe/graph, **GPU cuVS (cagra/ivfpq)** | WAND / BM25 | **per-index** (`Searcher`) |
| distance_function / distance_range | vector-only | n/a | **per-index** (algo params) |

The "generic" rows already exist in `searchplugin.Request` / the `plan_reader.go` shell; the
"per-index" rows are what the Hooks own. cagra/ivfpq differ from ivfflat only in the *kernel*
(load a GPU model blob from a hidden table, run cuVS) — which is exactly what their `Searcher` owns.

---

## 3. Governing principle — generic → shared package; index-specific → Hooks

Mirrors the framework (`AlgoPlugin`: `Catalog()`/`Compile()`/`Plan()`/`Idxcron()`; `SearchPlugin`:
`Search()`):

- **Generic, algorithm-independent code** → **shared package**; no `switch algo`, no per-index knowledge.
- **Index-specific code** → stays in the plugin, reached **only through Hooks**; never a free
function in `pkg/sql`, never a global `switch`.

`plan_expr.go` / `plan_reader.go` split accordingly (they do NOT stay as ivfflat free functions):

| File (today, ivfflat-only) | Generic half → shared pkg | Index-specific half → Hook |
|---|---|---|
| `plan_expr.go` (plan.Expr builders) | ALL → `pkg/indexplugin/planexpr` | node *assembly* (hidden tables, algo params, algo_exprs) → `Plan()` hook |
| `plan_reader.go` | `engine.Reader` loop + `relationScanner` (relation access, snapshot, partition) → `pkg/indexplugin/search/planreader` | query decode, hidden-table roles, search kernel → `Search()` hook (`Searcher`) |

Note (review fix): only the `engine.Reader` loop + `relationScanner` are shared by BOTH families.
The base-relation post-filter top-k (`compactRelationTop`/`filterRelationBatch`) is a **vector
post-filter helper** in the shared pkg that vector `Searcher`s MAY call; fulltext returns
already-ranked `(doc_id, score)` and does not use it.

---

## 4. Proto changes — `proto/plan.proto`

```proto
// message VectorIndexScan → message IndexSearchScan
message IndexSearchScan {
ObjectRef source_table = 1;
TableDef source_table_def = 2;
IndexDef index = 3; // index.IndexAlgo → dispatch; IndexAlgoParams → STATIC knobs
repeated IndexHiddenTableRef hidden_tables = 4; // was VectorIndexTableRef (role is a free string; fine for fulltext)
Expr query_payload = 5; // was query_vector: vector bytes OR text pattern
string distance_function = 6; // vector-only; fulltext leaves ""
OrderBySpec.OrderByFlag direction = 7; // ASC(distance) / DESC(score)
Expr candidate_limit = 8;
DistRange distance_range = 9; // vector-only; fulltext nil
repeated Expr pre_filters = 10;
repeated string included_columns = 11;
int64 threads_search = 15;
Snapshot scan_snapshot = 16;
bool post_filter_over_fetch = 17;
repeated Expr algo_exprs = 18; // NEW: DYNAMIC per-index exprs (folded generically)
repeated string algo_expr_names = 19; // NEW: parallel names; index-aligned with algo_exprs
// DELETED: initial_probe_count(12), first_round_limit(13), bucket_expand_step(14)
}
```
- STATIC knobs (nprobe, ef_search, itopk, search_width, k1, b) → each plugin reads them from
`index.IndexAlgoParams` JSON (already done at `plan_reader.go:183`), parsed ONCE (see §6).
- DYNAMIC knobs (IVF `first_round_limit`, an `Expr`) → declared by `Plan()` into
`algo_exprs`/`algo_expr_names`; folded by `execution.go`; read by name in `ShapeRequest`.
- Rename message `VectorIndexScan`→`IndexSearchScan`, `query_vector`→`query_payload`,
`VectorIndexTableRef`→`IndexHiddenTableRef`. Renumbering safe (new/pre-release).
- Regenerate `pkg/pb/plan`; update `pkg/sql/plan/deepcopy.go` (~425) + all refs.

---

## 5. Interfaces — `pkg/indexplugin/search/hooks.go`

### 5.1 `Request` (query payload; algo params opaque) — embed the core to avoid drift

```go
type Request struct {
RequestCore // embedded (review fix: no field duplication)
IncludedColumns []string
Direction plan.OrderBySpec_OrderByFlag
AlgoParams any // opaque, produced+consumed by the SAME plugin
}
type RequestCore struct {
QueryPayload []byte // was QueryVector: vector bytes OR serialized text query
QueryType plan.Type
ResultLimit uint64 // 0 ⇒ unbounded (fulltext stream-all)
CandidateBudget uint64
PreFilters []*plan.Expr
DistanceRange *plan.DistRange // vector-only; nil for fulltext
Membership []byte
HasMembership bool
Identity ScanIdentity
}
// REMOVED from shared struct: FirstRoundLimit, HasFirstRound (→ IVF's AlgoParams)
```

### 5.2 `Hooks` — request shaping + reader

```go
type ExprEval func(name string) (*plan.Literal, bool, error) // by-name algo_expr lookup

type Hooks interface {
// ShapeRequest: generic core + this plugin's algo_exprs → Request. Runs once per scalar scan
// AND once per correlated-APPLY row, so it must NOT re-parse IndexAlgoParams — static params
// are parsed once in Prepare* and carried on the spec/AlgoParams (review fix).
ShapeRequest(spec *plan.IndexSearchScan, core RequestCore, eval ExprEval) (Request, error)
// NewReader = one-liner over the shared base: planreader.New(proc, spec, req, &ivfSearcher{...})
NewReader(proc *process.Process, spec *plan.IndexSearchScan, req Request) (engine.Reader, error)
}
```

### 5.3 Shared base reader + the index-specific `Searcher` (STREAMING contract)

New `pkg/indexplugin/search/planreader` owns the generic `engine.Reader` (Read/Close loop,
`relationScanner`/`ScanRelation`, snapshot/identity, no-op `SetOrderBy`/`SetIndexParam`/`SetFilterZM`)
and the optional vector post-filter top-k helper. It drives a **streaming** searcher so a no-LIMIT
fulltext query never materializes the whole result set (review fix — this is the fulltext2
whole-index-in-RAM OOM class):

```go
// Implemented in each plugin (ivfflat, hnsw, cagra, ivfpq, fulltext, fulltext2).
type Searcher interface {
HiddenTableRoles() []string // relations the base reader opens
// Init once per generation; kernel may load a GPU model blob here (cagra/ivfpq).
Init(ctx context.Context, scan RelationScan) error
// Next yields the next chunk of ranked results, or ok=false at end. A bounded top-k
// Searcher returns one chunk then ok=false; fulltext no-LIMIT streams many chunks.
Next(ctx context.Context, mp *mpool.MPool) (out ResultChunk, ok bool, err error)
Close() error
}
type ResultChunk struct {
Keys []any // pk / doc_id
Scores []float64 // distance or BM25 score
Include map[string][]any // covered columns (optional)
}
```

`RelationScan` is the base reader's callback the `Searcher` uses to read hidden tables (relation
access, snapshot, partition shuffle stay generic). IVF's `searchPlanReader[T]`/`IvfSearchCursor`/
`relation_search.go` → ivfflat `Searcher`; cagra/ivfpq load their GPU model via `RelationScan` in
`Init` then cuVS-search in `Next`; fulltext's WAND/BM25 (streaming) → fulltext `Searcher`.

### 5.4 Node construction behind `Plan()` hook (feasible — infra already exists)

```go
// planplugin.Hooks — new method:
BuildIndexSearchScan(ctx PlanBuildContext) (*plan.IndexSearchScan, error)
```

`apply_indices.go` calls `plugin.Plan().BuildIndexSearchScan(ctx)` instead of per-algo files.
Confirmed feasible: `pkg/indexplugin/plan/hooks.go` already exposes a `CompilerContext`
(`ResolveVariable`) + a 23-method `PlanBuilder` facade + `VectorSortContext`, so the plugin can
resolve nprobe/threads and build the node **without importing `pkg/sql/plan`**.

### 5.5 GPU gating — cagra/ivfpq are first-class, not deferred

cagra/ivfpq implement the SAME `SearchPlugin`/`Searcher`. Their **GPU kernel** lives behind
`//go:build gpu` (their existing `search_gpu.go`); the plugin is blank-imported from
`pkg/indexplugin/all/all_gpu.go` (already the case). Therefore:
- **GPU build:** registered → planner emits `IndexSearchScan` → GPU `Searcher` runs cuVS.
- **CPU build:** not registered → `indexplugin.Get("cagra")` returns "unsupported index type:
cagra" **before DDL/query** — identical to today's TVF behavior. No regression, nothing missing.
- Keep `//go:build !gpu` CPU stubs only where the plugin package must compile on CPU; assertions
`var _ SearchPlugin` / `var _ Searcher` stay intact per build.

Their GPU *runtime* correctness is verified on a GPU box; the design, proto, plan, reader, and
registration all include them from day one.

---

## 6. `execution.go` — `pkg/sql/colexec/vectorscan` (→ rename `indexscan`)

Stays operator-side and family-agnostic; stops naming IVF fields, iterates generic `algo_exprs`.
Two generations, both funnel through `ShapeRequest`: **Scalar** (`PrepareScalar`→`RequestFromScalar`)
and **Correlated APPLY** (`PrepareCorrelatedExecution`→`EvalBatch`→`RequestAt`).

| Function | After (generic) |
|---|---|
| `newGenerationSpec` | `DeepCopyIndexSearchScan` + fold `PreFilters`/`DistanceRange`; **parse `IndexAlgoParams` ONCE** here and cache; does NOT fold `algo_exprs` (row-dependent) |
| `PrepareScalar` | fold `QueryPayload`, `CandidateLimit`, and **loop `spec.AlgoExprs`**; return folded `*IndexSearchScan` |
| `PrepareCorrelatedExecution` | executors for `QueryPayload`, `CandidateLimit`, **+ one per `spec.AlgoExprs`**; record `AlgoExprNames` |
| `EvalBatch` | eval `queryVec`, `limitVec`, then algo-expr vectors → `algoVecs` keyed by name |
| `RequestAt(row)` | build `RequestCore`; `hooks.ShapeRequest(spec, core, eval)`; `eval(name)`→`algoVecs[name]` at row |
| `RequestFromScalar` | build `RequestCore` from folded spec; `hooks.ShapeRequest(...)`; `eval(name)`→folded lit in `spec.AlgoExprs` |
| `requestForValues` | **deleted** — over-fetch + first-round now in the plugin's `ShapeRequest` |
| `Identity`, `Spec`, `Close`, `foldExpr` | unchanged (type rename only) |

`eval ExprEval` is the seam: execution.go evaluates a *named, untyped* expr list and hands the
plugin a by-name lookup; it never references `first_round_limit`/`nprobe`/`ef_search`. Package
rename `vectorscan`→`indexscan`, `apply/vector_source.go`→`apply/index_source.go`.

---

## 7. Package layout

```
pkg/indexplugin/
plan/hooks.go # + BuildIndexSearchScan
planexpr/ # NEW: generic plan.Expr builders (from ivfflat/plan_expr.go)
search/hooks.go # Request/RequestCore + Hooks(ShapeRequest,NewReader) + Searcher (streaming)
search/planreader/ # NEW: generic engine.Reader + relationScanner + optional post-filter top-k
all/all.go # ivfflat/hnsw/fulltext/fulltext2 (CPU)
all/all_gpu.go # cagra/ivfpq (GPU) — unchanged registration point
pkg/vectorindex/ivfflat/… # ivfflat Searcher + plugin/{search,plan}
pkg/vectorindex/hnsw/… # hnsw Searcher + plugin/{search,plan}
pkg/vectorindex/{cagra,ivfpq}/… # GPU Searcher behind //go:build gpu + plugin/{search,plan}
pkg/fulltext/… , pkg/fulltext2/… # fulltext Searcher (streaming WAND/BM25) + plugin/{search,plan}
pkg/sql/colexec/indexscan/execution.go # generic core + ShapeRequest
pkg/sql/colexec/table_function/table_function.go # delete ALL migrated case "*_search" arms
pkg/sql/plan/apply_indices*.go # call plugin.Plan().BuildIndexSearchScan
```

---

## 8. Migration plan (v1 = all six algorithms)

Ordered, independently-buildable commits:

1. **Mechanical rename** `VectorIndexScan`→`IndexSearchScan`, `query_vector`→`query_payload`,
`VectorIndexTableRef`→`IndexHiddenTableRef` (proto + pb regen + deepcopy + refs). No behavior change.
2. **Shared packages** — lift `planexpr` + `search/planreader` (streaming) out of ivfflat; ivfflat
re-expressed on them + its `Searcher`. Guarded by the 52 KB `plan_reader_test.go`.
3. **Hooks generalization** — `Request`/`Hooks` (`ShapeRequest`, embedded `RequestCore`, `AlgoParams`),
`execution.go` delegation, delete proto fields 12–14, add `algo_exprs`, parse-params-once.
4. **`Plan()` node construction** — add `BuildIndexSearchScan`; move ivfflat node build out of `pkg/sql/plan`.
5. **hnsw** — `SearchPlugin` (Searcher over graph hidden tables, `ef_search` from `IndexAlgoParams`);
emit `IndexSearchScan`; delete `case "hnsw_search"`.
6. **cagra + ivfpq** — `SearchPlugin` with GPU `Searcher` behind `//go:build gpu` (load model in
`Init`, cuVS in `Next`); registered via `all_gpu.go`; emit `IndexSearchScan`; delete
`case "cagra_search"`/`"ivfpq_search"`. CPU parity = "unsupported index type" as today.
7. **fulltext + fulltext2** — `SearchPlugin` (streaming WAND/BM25 `Searcher`; text `QueryPayload`;
docfilter→`MembershipFilter`; INCLUDE→`IncludedColumns`; nil `DistanceRange`; DESC; `ResultLimit==0`
streams); delete `case "fulltext_search"`/`"fulltext2_search"`.

After step 7 the `table_function.go:144` switch loses every `*_search` arm.

---

## 9. Risks & invariants

- **G-IDXPLUGIN**: no new `switch algo`/`IsXxxIndexAlgo` in `pkg/sql`/`pkg/catalog`; no per-algo
fields re-added to the shared proto; keep `var _ SearchPlugin`, add `var _ Searcher` per build.
- **GPU gating (cagra/ivfpq)**: SearchPlugin blank-imported ONLY from `all_gpu.go`; GPU kernel behind
`//go:build gpu`; CPU build must still fail-closed with "unsupported index type" (mo-dev §8.7 #4).
- **Streaming**: fulltext `ResultLimit==0` must stream via `Searcher.Next` — never materialize the
full result set (OOM class). Cover with a no-LIMIT fulltext2 reader test asserting bounded memory.
- **Correlated APPLY**: `ShapeRequest` runs per row; `algo_exprs` fold per row; static
`IndexAlgoParams` parsed once (not per row).
- **`AlgoParams any`**: opaque; base reader never inspects it.
- **Proto rename churn**: land step 1 as a standalone mechanical commit.

---

## 10. Lifecycle closure & interop — CDC, idxcron, restore, DROP

The search arc is one arc of the index-plugin closure `CREATE → CDC-sync → search → idxcron
(merge/rebuild) → restore/time-travel → DROP`. PR #27297 changed only the search arc and touched
**no** iscp/CDC, idxcron, or backup/restore code — those stay on their existing hooks
(`Catalog()`/`Compile()` for schema + DML-sync, `Idxcron()` for merge/rebuild) and remain uniform
across every algo. This design keeps that separation but pins the **interop invariants** so the
unified search arc can't diverge from the rest of the loop:

- **Single hidden-table source of truth.** `spec.HiddenTables` (and the `Searcher.HiddenTableRoles()`
it feeds) is built at plan time from the **Catalog hook** — the same catalog metadata the CDC
writer populates, idxcron rebuilds, and restore restores. No arc invents its own role knowledge;
a new hidden table added for CDC/idxcron is automatically visible to search.
- **CDC ⇄ search schema agreement.** For CDC-fed indexes (fulltext2 tail segments; ivfflat entries
rebuilt from ts=0 by the ISCP pipeline — `runtime.go:73`), the `Searcher` reads the *same* storage
tables the ISCP writer wrote. The design requirement: the `Searcher`'s decode of a hidden table
and the CDC writer's encode share one serialization contract (already true per-algo; must stay
co-located in the plugin, not split across the shared `planreader`).
- **idxcron rebuild vs. a running scan (MVCC).** idxcron atomically replaces hidden tables
(new physical table ids); the `Searcher` reads at `scan_snapshot`, so an in-flight rebuild never
corrupts a running scan. The known cross-CN `VectorIndexCache` eventual-staleness remains WON'T-FIX
(documented) and is unchanged by this design.
- **Restore / time-travel = snapshot-consistent read.** The `relationScanner` already clones the txn
op at `scan_snapshot.TS` (`plan_reader.go` ScanRelation), so a query after a restore, or an
AS-OF-timestamp query, reads the restored/historical hidden tables consistently. The shared
`planreader` MUST preserve this snapshot path when lifted — it is the closure link to restore/backup.
- **DROP** removes hidden tables via the Catalog hook; search simply fails registry lookup or reads
an empty set. No search-side action needed.

Net: CDC/idxcron/restore are **not re-architected**; they stay per-hook and uniform. The unified
search design only adds the invariants above so the search arc stays consistent with them. The
verification (§11) explicitly re-runs the CDC, idxcron/MERGE, and restore/time-travel closures per
migrated algo to prove no divergence.

## 11. Verification

1. `go build ./...` and `go build -tags gpu ./...`; `go vet -tags gpu ./pkg/sql/... ./pkg/vectorindex/... ./pkg/indexplugin/... ./pkg/fulltext...` (exit 0). CPU build of cagra/ivfpq plugins must still compile and stay unregistered.
2. CPU unit tests: shared `planreader` (relation scan, prefilter, streaming Next, top-k ASC & DESC);
per-plugin `ShapeRequest` (algo_exprs folding) + `Searcher`; `BuildIndexSearchScan` plan shape;
fulltext no-LIMIT streaming (bounded memory). Reuse ivfflat `plan_reader_test.go` as the
no-behavior-change gate for steps 2–4. cagra/ivfpq GPU `Searcher` = GPU-box runtime test.
3. G-IDXPLUGIN + GPU-gating greps stay clean.
4. Live: ivfflat/hnsw top-k, `fulltext`/`fulltext2` `MATCH … AGAINST` (incl. bm25 + no-LIMIT), and
(GPU box) cagra/ivfpq top-k — results identical to `main`; `EXPLAIN` shows `IndexSearchScan`,
no `*_search` TVF; correlated APPLY folds `algo_exprs` per row.
5. Benchmark ivfflat + fulltext2 filtered top-k vs `main` — no regression.
6. **Closure re-run (per migrated algo):** CDC-sync (insert/update/delete → search sees it),
idxcron MERGE/REBUILD (search reads rebuilt tables at snapshot), and restore/time-travel
(AS-OF query + post-restore query read consistent hidden tables). Reuse the existing
`pessimistic_transaction/*` and vector/fulltext CDC BVT suites — they must stay green, proving
the search-arc change did not diverge the rest of the loop.

---

## 12. Open items

- Shared-package home `pkg/indexplugin/{planexpr,search/planreader}` (RECOMMENDED — fulltext
participates, so not a vector-named pkg).
- `PlanBuildContext` exact shape — pin against current `apply_indices_ivfflat.go` inputs in step 4.

### Additional information

_No response_