From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dmytro Astapov <dastapov@gmail.com>
cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
Subject: Re: Regression: partial index with IS NOT NULL predicate not used for
 min/max optimization on NOT NULL columns
In-reply-to: 
 <CAFQUnFhmAi2hkgY=eEwokriYwZngG+2eeZY=BnBw+m=drDCAVg@mail.gmail.com>
References: 
 <CAKs4WVpEc2soWd5BXo6rkAXbTTWJZ5fWzBKMGo=XKRkOeMsRUA@mail.gmail.com>
 <CAFQUnFhmAi2hkgY=eEwokriYwZngG+2eeZY=BnBw+m=drDCAVg@mail.gmail.com>
Comments: In-reply-to Dmytro Astapov <dastapov@gmail.com>
	message dated "Wed, 24 Dec 2025 16:46:13 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <934305.1766596434.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 24 Dec 2025 12:13:54 -0500
Message-ID: <934306.1766596434@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/934306.1766596434%40sss.pgh.pa.us>
Precedence: bulk

Dmytro Astapov <dastapov@gmail.com> writes:
> When a column is defined as "NOT NULL" and a partial index exists with a
> predicate "WHERE column IS NOT NULL", the min/max aggregate optimization
> fails to use the partial index. This is a regression introduced in
> PostgreSQL 17.

Ugh.

> *Suggested fix*
> I think that when clause in the query matches a predicate in the partial
> index, it should not be discarded.

I don't like that proposal a bit.  It makes the behavior more complex
and less consistent, and probably re-introduces the problem complained
of in bug #17540.

The real problem here is that the operations are being done in the
wrong order; in particular making add_base_clause_to_rel responsible
for discarding qual conditions was a bad idea.  There are at least
two ways we could make it work less poorly:

1. postpone discarding of constant NOT NULL conditions till after
we check index predicates;

2. move discarding of constant NOT NULL conditions into
eval_const_expressions, which can fix the problem because that
is also applied to index predicates.

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:

regression=3D# EXPLAIN SELECT max(seqno) FROM test;
                                                  QUERY PLAN              =
                                     =

--------------------------------------------------------------------------=
-------------------------------------
 Result  (cost=3D0.32..0.33 rows=3D1 width=3D4)
   Replaces: MinMaxAggregate
   InitPlan minmax_1
     ->  Limit  (cost=3D0.29..0.32 rows=3D1 width=3D4)
           ->  Index Only Scan Backward using test_seqno_idx on test  (cos=
t=3D0.29..3050.29 rows=3D100000 width=3D4)
(5 rows)

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.

			regards, tom lane