Feedback-ID: ie3de48e3:Fastmail
Date: Wed, 14 Jan 2026 10:05:35 +0100
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: jian he <jian.universality@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: using index to speedup add not null constraints to a table
Message-ID: <202601140902.x7lh6iq42pwq@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <202601131715.cheonohttbyj@alvherre.pgsql>
Archived-At: <https://www.postgresql.org/message-id/202601140902.x7lh6iq42pwq%40alvherre.pgsql>
Precedence: bulk

On 2026-Jan-13, Álvaro Herrera wrote:

> However, if we do this, then I think computing notnull_attrs is
> pointless.  So we should only change the order: do this new check
> first, and if we find that any new not-null column is on a generated
> column, then we compute both notnull_virtual_attrs and notnull_attrs.
> No?

Oh, another thing we should do is have a first pass that verifies
whether all columns have an appropriate index, without scanning any of
them; only if we verify that they all have one (and no generated column
is involved) then we start scanning the indexes.  Otherwise we waste
time scanning one index and verify that it contains no null values, only
to realize that the next column does not have an appropriate index to
use, and thus we must scan the table.  Then the first index scan is
wasted work.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"The saddest aspect of life right now is that science gathers knowledge faster
 than society gathers wisdom."  (Isaac Asimov)