pingcap / pingcap/tidb

Partial Index is execluded from IndexJoin's choice

Open
#69,524 2 comments 0 reactions 0 assignees View on GitHub
affects-8.5 sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

This is intended in the first version.
But we see that this use case is live. We need to start an investigation about implementing this.

## Main scenario to investigate

The main case we should consider is IndexJoin with an outer join, where the inner side has a partial index and the partial-index predicate can be proven from the join condition.

For example:

```sql
CREATE TABLE l (
a INT
);

CREATE TABLE r (
a INT,
b INT,
INDEX idx_r_a(a) WHERE a IS NOT NULL
);

EXPLAIN FORMAT='plan_tree'
SELECT /*+ INL_JOIN(r) */ *
FROM l LEFT JOIN r ON l.a = r.a;
```

For matched rows of the right table, the normal equality condition `l.a = r.a` is null-rejecting on `r.a`, so `r.a IS NOT NULL` is guaranteed. The NULL-extended rows produced by the left outer join do not need to be read from `r`. Therefore, the partial index `idx_r_a` should be a valid candidate for the inner side of IndexJoin.

This should not apply to NULL-safe equality (`<=>`), because NULL can match NULL there and `r.a IS NOT NULL` is not guaranteed.

## Current behavior

Partial indexes are excluded from IndexJoin candidate paths. In particular, `AccessPath.IsIndexJoinUnapplicable()` currently treats partial-index paths as undetermined and rejects them for IndexJoin, even when the partial-index predicate can be proven.

## Things to verify

- Whether existing predicate pushdown already materializes the null-rejecting `IS NOT NULL` predicate for outer join inner sides.
- If not, whether outer join `EQ` join keys should derive `IS NOT NULL` for the null-supplying side before partial-index pruning.
- After the predicate is proven, IndexJoin path selection should allow the partial index while still rejecting unsafe cases such as `<=>`.
- Add regression tests for both the positive `=` case and the negative `<=>` case.

Contributor guide

Open the contributing guide

Research direction

Start at AccessPath.IsIndexJoinUnapplicable() and trace how predicate pushdown and IndexJoin path selection handle partial-index paths for outer joins. Verify whether the null-rejecting IS NOT NULL predicate is materialized for an = join, then add regression coverage for the positive = case and the negative <=> case. Done means safe partial-index candidates are accepted while unsafe NULL-matching cases remain rejected.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
backend, databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.