Hi, we needed recently to add not null constraint on some fields, and it struck me that it took long. Obviously - pg has to check the data. But it seems that it can't use index. Made 4 test tables: create table test (a int4, b timestamptz); insert into test (a,b) select i, now() - i * '1 minute'::interval from generate_series(1,10000000) i; create table test2 as select * from test; create table test3 as select * from test; create table test4 as select * from test; The idea for test is that i want to add "not null" clause to column a. So I made 3 indexes that might help. Maybe: Simple index on a: create index i1 on test2 (a); Index on a, but with condition that it indexes only null values create index i2 on test3 (a) where a is null; Index on a, but with condition that it indexes only not null values create index i3 on test4 (a) where a is not null; Theoretically, index i2 should be the most helpful - if it's valid, and empty, then it shouldn't be impossible to make the check for alter table using it, and thus reducing check time by "a lot". But: $ alter table test alter column a set not null; ALTER TABLE Time: 352.682 ms $ alter table test2 alter column a set not null; ALTER TABLE Time: 362.031 ms $ alter table test3 alter column a set not null; ALTER TABLE Time: 384.409 ms $ alter table test4 alter column a set not null; ALTER TABLE Time: 392.173 ms All alter tables took more or less the same time. So. I understand that to be 100% sure we need seq scan, but perhaps this requirement could be relaxed to use index if it's there, and is valid, has appropriate where and is empty? Best regards, depesz
