In generic query plans, we use lookup joins instead of constrained scans. But there is no lookup join equivalent to a limited constrained scan, so in generic plans we cannot push the limit into the lookup join. Supporting this might depend on #62471, though we think we could also use an overall limit for the entire lookup join instead of a per-lookup limit.
Here's an example using cockroach demo movr on v24.2.0-rc.1:
-- custom plan uses a limited constrained scan
EXPLAIN ANALYZE SELECT id FROM rides WHERE city = 'seattle' ORDER BY city, id LIMIT 10;
SET plan_cache_mode = force_generic_plan;
PREPARE p AS SELECT id FROM rides WHERE city = $1 ORDER BY city, id LIMIT 10;
-- generic plan cannot push the limit down into lookup join
EXPLAIN ANALYZE EXECUTE p ('seattle');
In generic query plans, we use lookup joins instead of constrained scans. But there is no lookup join equivalent to a limited constrained scan, so in generic plans we cannot push the limit into the lookup join. Supporting this might depend on #62471, though we think we could also use an overall limit for the entire lookup join instead of a per-lookup limit.
Here's an example using
cockroach demo movr
on v24.2.0-rc.1:Jira issue: CRDB-41156