Benchmark creating foreign key references for empty table column about safe-ecto-migrations HOT 2 CLOSED

-- SETUP

\timing;

drop table if exists testing_one;
drop table if exists testing_two;

create table testing_one (
  id bigint not null primary key,
  foo varchar(255),
  bar varchar(255)
);

create table testing_two (
  id bigint not null primary key,
  foo varchar(255),
  bar varchar(255)
);

INSERT INTO testing_one(id, foo, bar)
SELECT x, 'foo ' || cast(x as varchar(20)), 'bar ' || cast(x as varchar(20)) from generate_series(1,1000000) x;

-- TESTING WITH AN EMPTY TABLE

TRUNCATE TABLE testing_two;

-- TEST WITH SIMPLE REFERENCE
ALTER TABLE testing_two 
	ADD COLUMN testing_one_id bigint, 
	ADD CONSTRAINT testing_two_testing_one_id_fkey FOREIGN KEY (testing_one_id) 
	REFERENCES testing_one(id) ON DELETE CASCADE;
-- Time: 4.550 ms
  
CREATE INDEX CONCURRENTLY ON testing_two(testing_one_id);
-- Time: 4.091 ms
  
ALTER TABLE testing_two DROP IF EXISTS testing_one_id;
  
-- TEST WITH VALIDATE FALSE
ALTER TABLE testing_two 
	ADD COLUMN testing_one_id bigint, 
	ADD CONSTRAINT testing_two_testing_one_id_fkey FOREIGN KEY (testing_one_id) 
	REFERENCES testing_one(id) ON DELETE CASCADE NOT VALID;
-- Time: 1.045 ms
  
CREATE INDEX CONCURRENTLY on testing_two(testing_one_id);
-- Time: 2.033 ms
  
ALTER TABLE testing_two VALIDATE CONSTRAINT testing_two_testing_one_id_fkey;
-- Time: 0.748 ms

ALTER TABLE testing_two DROP IF EXISTS testing_one_id;



-- TEST WITH POPULATED DATA

TRUNCATE TABLE testing_two;

INSERT INTO testing_two(id, foo, bar)
SELECT x, 'foo ' || cast(x as varchar(20)), 'bar ' || cast(x as varchar(20)) from generate_series(1,1000000) x;

-- TEST WITH SIMPLE REFERENCE
ALTER TABLE testing_two 
	ADD COLUMN testing_one_id bigint, 
	ADD CONSTRAINT testing_two_testing_one_id_fkey FOREIGN KEY (testing_one_id) 
	REFERENCES testing_one(id) ON DELETE CASCADE;
-- 60.929 ms
  
CREATE INDEX CONCURRENTLY ON testing_two(testing_one_id);
-- Time: 240.110 ms
  
ALTER TABLE testing_two DROP IF EXISTS testing_one_id;
  
-- TEST WITH VALIDATE FALSE
ALTER TABLE testing_two 
	ADD COLUMN testing_one_id bigint, 
	ADD CONSTRAINT testing_two_testing_one_id_fkey FOREIGN KEY (testing_one_id) 
	REFERENCES testing_one(id) ON DELETE CASCADE NOT VALID;
-- Time: 0.507 ms
  
CREATE INDEX CONCURRENTLY on testing_two(testing_one_id);
-- Time: 207.736 ms
  
ALTER TABLE testing_two VALIDATE CONSTRAINT testing_two_testing_one_id_fkey;
-- Time: 44.663 ms

ALTER TABLE testing_two DROP IF EXISTS testing_one_id;