Thread

Re: Regression: partial index with IS NOT NULL predicate not used for min/max optimization on NOT NULL columns

Dmytro Astapov <dastapov@gmail.com> — 2025-12-24T18:43:06Z

On Wed, Dec 24, 2025 at 5:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I think #2 is the better answer ... and, as it happens, that got done
> recently (in e2debb643).  So HEAD no longer exhibits the problem you
> show:
> <snip the EXPLAIN>
>
> However, there is still a check for constant-true conditions
> in add_base_clause_to_rel, because the author argued that there
> are edge cases that still justify it.  I am wondering though if
> your example can be modified so that it still misbehaves in HEAD.
> That would be ammunition to remove the check altogether, which
> I still think is what we should do.  It's a fundamental structural
> error to do this there.
>

Thank you for such a quick reply. This is truly Christmas come early to see
that this is already fixed in HEAD.

I read  e2debb643, and it looks like it fixes the issue I reported with
two-prong approach:

Both query clauses and index predicates go through "eval_const_expressions":
- Query clauses are reduced in "subquery_planner" before "query_planner"
- Index predicates are independently reduced in "get_relation_info"
(plancat.c lines 453-456)

So If a query's "col IS NOT NULL" is reduced to TRUE (because col has a
"NOT NULL" constraint), the partial index predicate "WHERE col IS NOT NULL"
is also reduced to NIL, making the index effectively non-partial (if that
is the right term to use).

I poked around a bit, but was unable to construct a counterexample that
you've hinted at.

Maybe the potential fragility comes from having both query clauses and
index predicates going through the same reduction logic, and a change to
either path could break the "status quo", but seems (to me, at least, which
is not worth a lot :) like no easy way to "break" it exists now.

Anyway, thanks again!