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
