PostgreSQL ON DELETE performance analysis - SET NULL vs. SET DEFAULT vs. CASCADE

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!