madsmtm · April 25, 2019 13:20
diff --git a/postgresql-on-delete-performance.sql b/postgresql-on-delete-performance.sql
 -- Initialize tables
 DROP TABLE IF EXISTS table1, table2;
 CREATE TABLE table1 (col integer PRIMARY KEY);
 CREATE TABLE table2 (
 	col integer REFERENCES table1(col)
 	ON DELETE SET DEFAULT -- Experiment with different ON DELETE clauses
 );

 -- Optionally create an index
 CREATE INDEX ON table2(col);

 -- Generate test data
 INSERT INTO table1(col) SELECT generate_series(1, 1000000); -- Number of test rows
 INSERT INTO table2(col) SELECT col FROM table1;

 -- Delete everything in table1, which in turn cascades to table2.
 EXPLAIN ANALYSE DELETE FROM table1;

 -- My results were as follows, using PostgreSQL 10:
 --                        | SET NULL | SET DEFAULT | CASCADE
 -- -----------------------+----------+-------------+---------
 --  10000 rows, no index  | 6.8s     | 13.8s       | 3.9s
 --  1000000 rows, indexed | 21-22s   | 35-40s      | 12-13s

 -- Conclusion: Use SET NULL over SET DEFAULT, when the default is NULL!