Repository navigation
SEARCH_INDEX loads every matching record before LIMIT applies #9895
Description
Problem
SELECT ... WHERE SEARCH_INDEX('idx', 'query') = true LIMIT 200 returns at most 200 rows, but it still matches, sorts and loads every matching record first. SQLFunctionSearchIndex.searchFromTarget(...) builds the full match map, copies it into a list, sorts it by score and calls lookupByRID on every entry before the iterator is handed to FetchFromIndexedFunctionStep. On a broad query this is a lot of heap and I/O for rows that are thrown away.
FullTextSearch.search(typeIndex, query, limit) already accepts a limit, but the SQL path passes -1.
Proposed enhancement
- Lazy loading: keep the sorted
(RID, score)entries and resolve each record in the iterator, soLIMITstops the record loading. This is always safe. - Limit pushdown (top-k by score instead of a full sort), only when it cannot change the result:
- the condition is a single AND block (no OR split into parallel branches),
- there is no residual filter after the indexed function,
- and there is either no
ORDER BYorORDER BY $score DESC.
Tests: same results as today with and without LIMIT, ORDER BY $score, OR-ed conditions and residual filters; the number of records loaded is bounded by the limit when the pushdown applies.
Activity
lvca commentedon Oct 13, 2026
Field evidence from a 3-node cluster under a full-text heavy workload: in one day, about two thirds of the logged
OutOfMemoryErrors were raised insideSEARCH_INDEXbeforeLIMITapplied:SQLFunctionSearchIndex.searchFromTarget->LocalDatabase.lookupByRID(loading every match)LSMTreeFullTextIndex.getPostings(collecting postings)LSMTreeFullTextIndex.computeBM25ScoresEvery node restarted several times that day. So this is a stability issue as much as a latency one: raising the priority.
Two notes from the same workload:
SEARCH_INDEXconditions plus a residual filter (deleted_at IS NULL), so the limit pushdown in point 2 would not apply. Lazy record loading (point 1) still bounds the record loads by the rows the filter lets through.LIMITeligible rows sit in the score order, not to the total number of matches.test(#9895): repeated SEARCH_INDEX and residual-filter scores, matche…
review(#9895): guard the skip into the complete search; document the …
review(#9895): LIMIT 0 searches nothing; EXPLAIN reports the rows the…
review(#9895): document the per-bucket size of the best matches and t…
Merge pull request #9906 from ArcadeData/worktree-arcadedb-9893-9894-…
lvca commentedon Oct 13, 2026
Fixed in #9906 (milestone 26.11.1).
SEARCH_INDEX(...) ... LIMIT nused to load every matching record before applying the limit. Now:LIMIT(or a filter that stops early) also stops the loading. This applies to every query shape, includingSEARCH_INDEX(...) OR SEARCH_INDEX(...) AND <filter> LIMIT n.SEARCH_INDEXcondition with no other filter, noORDER BYor exactlyORDER BY $score DESC, and noGROUP BY/DISTINCT/aggregates. Here the search keeps only the bestSKIP + LIMITmatches and skips the sort.EXPLAINshows(best N needed)on the fetch step.Results are the same as before; only the work done to get them is smaller. The documentation's full-text page describes this.
❤️ Thanks for helping make ArcadeDB better! ArcadeDB is free and open source, sustained by its community. If it's useful to you or your company, please consider becoming a sponsor to keep development going.
fix(ArcadeData#9893, ArcadeData#9894, ArcadeData#9895): SEARCH_INDEX …
fix(#9893, #9894, #9895): SEARCH_INDEX and the index replay stop on c…
test(#9895): repeated SEARCH_INDEX and residual-filter scores, matche…
review(#9895): guard the skip into the complete search; document the …
review(#9895): LIMIT 0 searches nothing; EXPLAIN reports the rows the…
review(#9895): document the per-bucket size of the best matches and t…