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

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема Is there any technical reason why "alter table .. set not null" can't use index?
Дата
Msg-id 20210908045919.GA29476@depesz.com
обсуждение исходный текст
Ответы Re: Is there any technical reason why "alter table .. set not null" can't use index?  (Alexander Kukushkin <cyberdemn@gmail.com>)
Список pgsql-general
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




В списке pgsql-general по дате отправления:

Предыдущее
От: Ninad Shah
Дата:
Сообщение: Re: prevent WAL replication to fill filesystem
Следующее
От: Alexander Kukushkin
Дата:
Сообщение: Re: Is there any technical reason why "alter table .. set not null" can't use index?