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'
Date: Sun, 03 Aug 2025 13:19:40 -0400
Message-ID: <516624.1754241580@sss.pgh.pa.us> (raw)
In-Reply-To: <19007-4cc6e252ed8aa54a@postgresql.org>
References: <19007-4cc6e252ed8aa54a@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, bryfox@gmail.com, guofenglinux@gmail.com
Subject: Re: BUG #19007: Planner fails to choose partial index with spurious 'not null'
In-Reply-To: <516624.1754241580@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox