planner: use JSON/MVIndex element-level stats to estimate JSON array membership predicates
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Feature Request
**Is your feature request related to a problem? Please describe:**
We would like TiDB to provide more accurate selectivity estimation for JSON array membership predicates.
Today, TiDB can use MVIndex as an access path for positive JSON membership predicates such as `MEMBER OF`, `JSON_CONTAINS`, and `JSON_OVERLAPS`. However, there are query shapes where the predicate cannot be used as an MVIndex access path, but the optimizer still needs accurate selectivity estimation.
For example:
```sql
SELECT b.id
FROM base_table AS b
LEFT JOIN json_attr_table AS j
ON b.id = j.base_id
WHERE b.tenant_id = ?
AND (
NOT JSON_OVERLAPS(j.json_values->'$', JSON_ARRAY('v1', 'v2', 'v3'))
OR j.json_values IS NULL
)
ORDER BY b.id
LIMIT 50;
```
In this query, the MVIndex can represent the positive match set:
```sql
JSON_OVERLAPS(j.json_values->'$', JSON_ARRAY(...))
```
but it cannot be used directly as an access path for the complement predicate:
```sql
NOT JSON_OVERLAPS(...) OR j.json_values IS NULL
```
Even so, if TiDB has JSON/MVIndex element-level statistics for `j.json_values`, the optimizer should be able to use those statistics to estimate the selectivity of the predicate.
Without this, TiDB may fall back to generic/default selectivity estimation, which can lead to a large cardinality gap and then a poor join strategy, such as choosing row-by-row IndexJoin when a bulk join would be cheaper.
**Describe the feature you'd like:**
Use JSON/MVIndex element-level statistics to estimate JSON array membership predicates more accurately, even when the predicate cannot be used as an MVIndex access path.
This should help estimate predicates such as:
```sql
JSON_OVERLAPS(json_path, JSON_ARRAY(...))
JSON_CONTAINS(json_path, JSON_ARRAY(...))
MEMBER OF(...)
NOT JSON_OVERLAPS(json_path, JSON_ARRAY(...))
NOT JSON_CONTAINS(json_path, JSON_ARRAY(...))
```
including nullable/outer-join forms such as:
```sql
NOT JSON_OVERLAPS(json_path, JSON_ARRAY(...)) OR json_col IS NULL
```
The goal is better predicate selectivity and join cardinality estimation, so the optimizer can choose between IndexJoin, HashJoin, MergeJoin, IndexMerge, and table scan plans more reliably.
**Describe alternatives you've considered:**
The current workaround is to use query-shape-specific hints or SQL bindings, for example forcing HashJoin for affected negative JSON membership query patterns.
This works as a mitigation, but applications with generic query generation should not need to manually classify every combination of JSON path, value list, join type, and filter shape.
**Teachability, Documentation, Adoption, Migration Strategy:**
Documentation could clarify that MVIndex is currently useful as an access path for positive JSON membership predicates, while negative/complement predicates may still need JSON/MVIndex element-level statistics for selectivity estimation.
Contributor guide
Research direction
The issue names no files or tests. Start by locating TiDB planner selectivity estimation for JSON/MVIndex membership predicates, then compare positive, negative, and nullable outer-join forms. Done means element-level statistics improve selectivity and join cardinality estimates for the listed predicate shapes, with plan choices becoming more reliable.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100