Partial Index is execluded from IndexJoin's choice
- 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
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