pingcap / pingcap/tidb

planner: use JSON/MVIndex element-level stats to estimate JSON array membership predicates

Open
#69,727 0 comments 0 reactions 0 assignees View on GitHub
sig/planner type/feature-request
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.