Thread: Is there any technical reason why "alter table .. set not null" can't use index?

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

On Wed, Sep 08, 2021 at 07:09:31AM +0200, Alexander Kukushkin wrote:
> Hi,
> 
> On Wed, 8 Sep 2021, 06:59 hubert depesz lubaczewski, <depesz@depesz.com>
> wrote:
> 
> > 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.
> >
> 
> It can't use the index, but can use an already existing CHECK CONSTRAINT,
> that could be created as NOT VALID and validated without holding heavy
> locks. After adding not null you can drop the constraint.

Thanks. Forgot about these.

Best regards,

depesz

Would it help to create a new not null column in the target table, and then update the table by copying values from old
columnto the new, not null column? Of course you’d have to ignore errors, etc. but wouldn’t that perform at enough for
yourneeds? 

Sent from my iPhone

> On Sep 8, 2021, at 1:15 AM, hubert depesz lubaczewski <depesz@depesz.com> wrote:
>
> On Wed, Sep 08, 2021 at 07:09:31AM +0200, Alexander Kukushkin wrote:
>> Hi,
>>
>>> On Wed, 8 Sep 2021, 06:59 hubert depesz lubaczewski, <depesz@depesz.com>
>>> wrote:
>>>
>>> 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.
>>>
>>
>> It can't use the index, but can use an already existing CHECK CONSTRAINT,
>> that could be created as NOT VALID and validated without holding heavy
>> locks. After adding not null you can drop the constraint.
>
> Thanks. Forgot about these.
>
> Best regards,
>
> depesz
>
>
>

On Fri, Sep 10, 2021 at 01:26:46PM -0400, Gus Spier wrote:
> Would it help to create a new not null column in the target table, and
> then update the table by copying values from old column to the new,
> not null column? Of course you’d have to ignore errors, etc. but
> wouldn’t that perform at enough for your needs?

Why would you think that adding new column, with constraint, and copying
data would be faster?

depesz