Postgres: W jaki sposób funkcja SET NOT NULL jest „bardziej wydajna” niż ograniczenie CHECK

W dokumentacji PostgreSQL dla Ograniczeń jest napisane

Ograniczenie niepuste jest funkcjonalnie równoważne z utworzeniem ograniczenia sprawdzającego CHECK (column_name IS NOT NULL), ale w PostgreSQL tworzenie wyraźnego ograniczenia niepustego jest bardziej wydajne.

zastanawiam się

• Co dokładnie oznacza „bardziej wydajny”?
• Jakie są niekorzystne skutki używania CHECK (column_name IS NOT NULL)zamiast SET NOT NULL?

Chcę mieć możliwość dodania NOT VALID CHECKograniczenia i zweryfikowania go osobno (więc AccessExclusiveLockwstrzymanie trwa tylko przez krótki czas w celu dodania ograniczenia, a następnie ShareUpdateExclusiveLockwstrzymanie dla dłuższego kroku sprawdzania poprawności):

ALTER TABLE table_name
  ADD CONSTRAINT column_constraint
  CHECK (column_name IS NOT NULL)
  NOT VALID;
ALTER TABLE table_name
  VALIDATE CONSTRAINT column_constraint;

Zamiast:

ALTER TABLE table_name
  ALTER COLUMN column_name
  SET NOT NULL;

Moje dzikie przypuszczenie: „bardziej wydajny” oznacza „mniej czasu potrzeba na sprawdzenie” (przewaga czasowa). Może to również oznaczać „potrzeba mniej pamięci do wykonania testu” (przewaga miejsca). Może to również oznaczać „ma mniej skutków ubocznych” (takich jak nie blokowanie czegoś lub blokowanie go na krótsze okresy czasu) ... ale nie mam sposobu, aby poznać lub sprawdzić tę „dodatkową zaletę”.

Nie mogę wymyślić łatwego sposobu na sprawdzenie możliwej przewagi przestrzeni (co, jak sądzę, nie jest tak ważne, gdy pamięć jest obecnie tania). Z drugiej strony nie jest tak trudno sprawdzić możliwą przewagę czasową: po prostu utwórz dwie tabele, które są takie same, z jedynym wyjątkiem ograniczenia. Wstaw wystarczająco dużą liczbę wierszy, powtórz kilka razy i sprawdź czasy.

Oto konfiguracja tabeli:

CREATE TABLE t1
(
   id serial PRIMARY KEY, 
   value integer NOT NULL
) ;

CREATE TABLE t2
(
  id serial PRIMARY KEY,
  value integer
) ;

ALTER TABLE t2
  ADD CONSTRAINT explicit_check_not_null
  CHECK (value IS NOT NULL);

To jest dodatkowy stolik, służący do przechowywania czasów:

CREATE TABLE timings
(
   test_number integer, 
   table_tested integer /* 1 or 2 */, 
   start_time timestamp without time zone,
   end_time timestamp without time zone,
   PRIMARY KEY(test_number, table_tested)
) ;

I to jest test przeprowadzony przy użyciu pgAdmin III i funkcji pgScript .

declare @trial_number;
set @trial_number = 0;

BEGIN TRANSACTION;
while @trial_number <= 100
begin
    -- TEST FOR TABLE t1
    -- Insert start time
    INSERT INTO timings(test_number, table_tested, start_time) 
    VALUES (@trial_number, 1, clock_timestamp());

    -- Do the trial
    INSERT INTO t1(value) 
    SELECT 1.0
      FROM generate_series(1, 200000) ;

    -- Insert end time
    UPDATE timings 
       SET end_time=clock_timestamp() 
     WHERE test_number=@trial_number and table_tested = 1;

    -- TEST FOR TABLE t2
    -- Insert start time
    INSERT INTO timings(test_number, table_tested, start_time) 
    VALUES (@trial_number, 2, clock_timestamp());

        -- Do the trial
    INSERT INTO t2(value) 
    SELECT 1.0
    FROM generate_series(1, 200000) ;

    -- Insert end time
    UPDATE timings 
       SET end_time=clock_timestamp() 
     WHERE test_number=@trial_number and table_tested = 2;

    -- Increase loop counter
    set @trial_number = @trial_number + 1;
end 
COMMIT TRANSACTION;

Wynik podsumowano w następującym zapytaniu:

SELECT
    table_tested, 
    sum(delta_time), 
    avg(delta_time), 
    min(delta_time), 
    max(delta_time), 
    stddev_pop(delta_time) 
FROM
    (
    SELECT
        table_tested, extract(epoch from (end_time - start_time)) AS delta_time
    FROM
        timings
    ) AS delta_times
GROUP BY
    table_tested 
ORDER BY
    table_tested ;

Z następującymi wynikami:

table_tested | sum     | min   | max   | avg   | stddev_pop
-------------+---------+-------+-------+-------+-----------
           1 | 176.740 | 1.592 | 2.280 | 1.767 | 0.08913
           2 | 177.548 | 1.593 | 2.289 | 1.775 | 0.09159

Wykres wartości pokazuje ważną zmienność:

W praktyce więc SPRAWDŹ (kolumna NIE JEST NULLOWANA) jest bardzo nieznacznie wolniejsza (o 0,5%). Jednak ta niewielka różnica może wynikać z dowolnego losowego powodu, pod warunkiem, że zmienność czasów jest znacznie większa. Nie jest to więc statystycznie istotne.

Z praktycznego punktu widzenia bardzo zignorowałbym „bardziej wydajne” NOT NULL, ponieważ tak naprawdę nie widzę, żeby miało to znaczenie; mając na uwadze, że uważam, że brak aneksu AccessExclusiveLockjest zaletą.