Typically, when I set a column not null, if it doesn’t have an index then I add it first, so that postgres can (hopefully) use the index while doing the table scan while locking the table, so that the table is locked for a shorter amount of time.
I want to set several columns not null, like so:
alter table foos alter column bar1 set not null alter column bar2 set not null alter column bar3 set not null alter column bar4 set not null;
If I make a multicolumn index for these columns, will postgres use it when scanning the locked table before making this alteration?
CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);
What if I made a partial index on IS NULL (or, IS NOT NULL)
CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;