CBO chooses multithreaded scan over SecondaryIndex for string-attr equality at scale; scan cost estimate ignores row width (300-1000x slower than hinted SI)

Bug Description

For string-attribute equality SELECTs, the cost-based optimizer switches from SecondaryIndex to a multithreaded full scan once a table grows past a row-count threshold (between 5M and 15M rows on a 32-thread server in our tests) — and the scan cost estimate appears to be blind to row width, so on wide tables the chosen plan is 300–1,000× slower than the SecondaryIndex it declines to use. The SI itself is healthy: forcing it with /*+ SecondaryIndex(attr) */ works and is fast.

Measurements (all RT tables, default settings, Manticore 25.0.0, 32-thread server)

Table	rows	unhinted `WHERE sattr='x' LIMIT 3`	hinted	CBO choice (SHOW META)
narrow (text + 1 string attr)	5M	0.014s	—	`SecondaryIndex (100%)`
narrow (same schema)	15M	0.064s	—	scan (no `index` row)
wide (text + 4 string attrs, ~+240B/row)	15M	1.52s	0.005s	scan
production table (~60 attrs incl. JSON/MVA, ~47GB)	15M	23–27s (156s for COUNT-style no-LIMIT)	0.024s	scan

So the row-count crossover itself might be defensible on narrow tables (0.064s scan), but because the estimate ignores per-row cost, the same decision on wide tables is catastrophically wrong.

Strong evidence the crossover is the multithreaded-scan preference

OPTION threads=1 (no hint) makes the CBO pick the SecondaryIndex again on both the synthetic wide table and the production table:

SELECT id FROM big_table WHERE sattr='value' LIMIT 3 OPTION threads=1;
SHOW META;   -- index: sattr:SecondaryIndex (100%), 0.034s on the production table

Notes: COUNT(*) WHERE sattr='x' stays fast via the Precalc path; numeric-attribute equality keeps choosing SI at all sizes — only string-attr SELECTs are affected, which makes this confusing to diagnose in production.

Repro script

# pymysql against a stock searchd; takes ~5 min
import random, string, pymysql
c = pymysql.connect(host="127.0.0.1", port=9306, autocommit=True).cursor()
c.execute("CREATE TABLE w (title text, sattr string, s2 string, s3 string, s4 string)")
random.seed(9); AB = string.ascii_lowercase
rs = lambda k: "".join(random.choices(AB, k=k))
f2, f3, f4 = rs(80), rs(90), rs(70)
n = 0
while n < 15_000_000:
    vals, args = [], []
    for j in range(5000):
        vals.append("(%s,%s,%s,%s,%s,%s)"); args += [n+j+1, "t", rs(24), f2, f3, f4]
    c.execute("INSERT INTO w (id,title,sattr,s2,s3,s4) VALUES " + ",".join(vals), args)
    n += 5000
c.execute("FLUSH RAMCHUNK w")
# pick any existing value, then compare:
#   SELECT id FROM w WHERE sattr='<v>' LIMIT 3;                       -- ~1.5s, scan
#   SELECT id FROM w WHERE sattr='<v>' LIMIT 3 /*+ SecondaryIndex(sattr) */;  -- ~5ms
#   SELECT id FROM w WHERE sattr='<v>' LIMIT 3 OPTION threads=1;      -- ~50ms, picks SI

Version

Manticore 25.0.0 ce3c27828@26032712 (columnar 13.0.0) (secondary 13.0.0) (knn 13.0.0)
Ubuntu, searchd threads = 32, secondary_indexes = 1, RT tables, row-wise storage

Related: commit d96ec6b ("boost string filter cost in CBO", 6.2.0) suggests string filters were intended to prefer SI. Happy to run further diagnostics.

Additional evidence that this isn't string-specific: same table, MVA attribute (multi, ~300k distinct value-sets across 13.7M docs, SI rebuilt and showing Enabled=1 / 100% in SHOW TABLE INDEXES):

SELECT id FROM t WHERE ANY(mva_attr)=13 ORDER BY pop_attr DESC LIMIT 30
-- 4m54s unhinted (multithreaded scan chosen)
-- 0.044s with /*+ SecondaryIndex(mva_attr) */
-- 0.101s with OPTION threads=1 (no hint -- the CBO picks the SI on its own
--        once the multithreaded-scan option is removed, consistent with the
--        crossover hypothesis in the original report)

So the cost-model issue appears to apply to all attribute filters, not only strings.

@hashtagaturn

We tried to reproduce this with the public script but couldn't reproduce the unhinted scan plan.

Tested environments:

macOS, manticoresearch/manticore:25.0.0, amd64 container, searchd.threads=32, secondary_indexes=1
Ubuntu x86_64 host with real 32 CPUs, Docker manticoresearch/manticore:25.0.0, searchd.threads=32, secondary_indexes=1

In both cases, the exact 15M-row wide table:

CREATE TABLE w (title text, sattr string, s2 string, s3 string, s4 string);

kept choosing:

sattr:SecondaryIndex (100%)

for the unhinted query. We also tested missing values, first/middle/last existing values, narrow all-x, wide all-x, and an MVA + ORDER BY control. NO_SECONDARYINDEX does force the no-index scan path, so the measurement can distinguish scan vs SI, but CBO did not choose scan by itself.

@hashtagaturn, could you please provide more details about the original environment?

full SHOW VERSION
SHOW SETTINGS and SHOW VARIABLES
exact SHOW TABLE w STATUS after load
exact SHOW TABLE w INDEXES
exact query value used for sattr
full SHOW META after unhinted / hinted / OPTION threads=1
whether any deletes/replaces/OPTIMIZE happened before testing
host CPU/RAM/storage details and whether this was Docker, package install, or custom build

Navigation Menu

Repository navigation

CBO chooses multithreaded scan over SecondaryIndex for string-attr equality at scale; scan cost estimate ignores row width (300-1000x slower than hinted SI) #4626

Bug Description

Measurements (all RT tables, default settings, Manticore 25.0.0, 32-thread server)

Strong evidence the crossover is the multithreaded-scan preference

Repro script

Version

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

CBO chooses multithreaded scan over SecondaryIndex for string-attr equality at scale; scan cost estimate ignores row width (300-1000x slower than hinted SI) #4626

Description

Bug Description

Measurements (all RT tables, default settings, Manticore 25.0.0, 32-thread server)

Strong evidence the crossover is the multithreaded-scan preference

Repro script

Version

Activity

hashtagaturn commented on Jun 12, 2026

sanikolaev commented on Jul 9, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions