Re: using index to speedup add not null constraints to a table

From: Baji Shaik <baji(dot)pgdev(at)gmail(dot)com>
To: jian he <jian(dot)universality(at)gmail(dot)com>
Cc: Álvaro Herrera <alvherre(at)kurilemu(dot)de>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: using index to speedup add not null constraints to a table
Date: 2026-11-17 18:28:58
Message-ID: CA+fm-RPCcMxpHVJrUZWKwMPtX_D2eq=2ArAqU7U1GsJrGyiCDg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello Jian,

On Tue, Sep 29, 2026 at 7:02 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> Hi.
> Please check the attached v16.
>

I rebased v16 against current master and ran it through some
correctness tests, including the indcheckxmin/HOT scenario your own
test covers. Looks good to me.

Alvaro's point about checking for generated columns before computing
notnull_attrs still looks open; the other point (verifying every
column has a usable index before scanning any) looks addressed now in
index_check_notnull().

One small thing I noticed in index_check_notnull():

+ attnum = (indexStruct->indkey.values[0]);
+
+ if (list_member_int(notnull_attnums, attnum) &&
+ !list_member_int(attnums, attnum))
+ {
+ attnums = lappend_int(attnums, attnum);
+ idxs = lappend_oid(idxs, indexoid);
+ }

This takes the first qualifying index per column in
RelationGetIndexList() order, with no preference for e.g. the primary
key or the narrowest index. Was that a deliberate simplification, or
worth tightening later?

Separately, I've been working on a related patch: ALTER TABLE ...
VALIDATE CONSTRAINT also always does a full table scan to check a
NOT VALID CHECK constraint, even when an existing btree index on the
column already proves it. For a simple "column OP constant" CHECK on a
NOT NULL column that's the leading column of a btree index, it scans
the index for the negated condition instead, falling back to the
existing full scan otherwise. Same underlying idea as this patch (use
an existing index to skip a validation scan), just for CHECK instead
of NOT NULL, so it seemed worth raising here rather than as a separate
thread. Attached are v16 (unchanged) and v1 of mine as 0002.

Thoughts?

Thanks,
Baji Shaik

Attachment Content-Type Size
v16-0001-using-indexscan-to-speedup-add-not-null-constraints.patch application/octet-stream 20.8 KB
v1-0002-Optimize-VALIDATE-CONSTRAINT-for-simple-indexed-colu.patch application/octet-stream 16.4 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Previous Message Haibo Yan 2026-11-17 18:27:10 Re: [PATCH v1] pg_stash_advice: Reject embedded NUL bytes in persistence files