I've tried to find the root cause, and this is my (possibly incorrect) analysis
The regression was introduced by commit b262ad440ed ("Add better handling of redundant IS [NOT] NULL quals", 2024-01-23).
This commit added an optimization to remove "IS NOT NULL" restriction clauses when the column is defined as "NOT NULL", since such clauses are always true.
This optimization was added to fix Bug #17540 related to poor index choice in min/max queries.
However, this optimization has an unintended side effect on partial index matching:
1. The min/max optimization (in "planagg.c") rewrites "SELECT max(col)" into a subquery with the clause "WHERE col IS NOT NULL" added to handle NULL values correctly.
2. The new optimization in `add_base_clause_to_rel()` recognizes that the added clause "col IS NOT NULL" is always true (since "col" is "NOT NULL" according to the table schema) and discards the clause before adding it to "baserestrictinfo".
3. Later, "check_index_predicates()" attempts to prove that the partial index predicate ("WHERE seqno IS NOT NULL") is implied by the query's clauses in "baserestrictinfo".
4. Since the "IS NOT NULL" clause was discarded, the partial index predicate cannot be proven, and the index is not marked as usable ("predOK" remains false).
Suggested fix
I think that when clause in the query matches a predicate in the partial index, it should not be discarded.
Then fix could be to modify `add_base_clause_to_rel()` to retain `IS NOT NULL` clauses when they match a partial index predicate on the same column. This preserves the intended fix for Bug #17540 while restoring partial index usability.
I am attaching the patch against REL_18_1 which attempts to do this.
Testing
- The attached patch resolves the reported issue
- All 228 standard regression tests pass
