From: Tom Lane <tgl@sss.pgh.pa.us>
To: bryfox@gmail.com
cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
Subject: Re: BUG #19007: Planner fails to choose partial index with spurious
 'not null'
In-reply-to: <19007-4cc6e252ed8aa54a@postgresql.org>
References: <19007-4cc6e252ed8aa54a@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sun, 03 Aug 2025 16:15:10 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <516623.1754241580.1@sss.pgh.pa.us>
Date: Sun, 03 Aug 2025 13:19:40 -0400
Message-ID: <516624.1754241580@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/516624.1754241580%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> I'm not sure if this is a bug, but the behavior was unexpected to me and
> changed since v16. Documentation doesn't mention this as far as I can see.
> This example has a partial index over one column where another column is not
> null. The latter column is in fact 'not null' in the schema.
> Prior to v17, this index would be used; in v17, the planner will choose a
> sequential scan instead.

Yeah.  What is happening is that the query's "flag is not null"
condition is thrown away as redundant fairly early in planning,
and then it's not available to match to the index's predicate,
so the index doesn't appear to be applicable.

I had thought that e2debb643 would fix this in HEAD, since it moved
the throw-it-away behavior into eval_const_expressions which is also
applied to index predicates.  Testing shows not, which feels like a
bug.  It is not okay that eval_const_expressions' behavior varies
depending on context; we need it to act the same on index
expressions/predicates as on query expressions.  Maybe we should
give up on trying to pre-apply eval_const_expressions to the
relcache's copies of the index expressions, and just do that part
during plancat's collection of the info?

(I'm also quite displeased that e2debb643 didn't nuke
restriction_is_always_true/false.  But that's a separate discussion.)

			regards, tom lane