POSTGRESQL: INDEXES AND FOREIGN KEYS

postgres_index_foreign_keys.md

Step 1:

CREATE TABLE a (
    a_id int PRIMARY KEY
);

Step 2:

CREATE TABLE b (
  b_id    int,
  a_id    int  REFERENCES a(a_id)
                ON UPDATE CASCADE
                ON DELETE CASCADE
);

Step 3:

INSERT INTO a
  SELECT  x
  FROM    generate_series(1, 5000000) AS x;

Step 4:

INSERT INTO b
  SELECT  x, x
  FROM    generate_series(1, 5000000) AS x;

Step 5:

explain analyze DELETE FROM a WHERE a_id = 10;

Step 6:

CREATE INDEX idx_b ON b (a_id);

Step 7:

explain analyze DELETE FROM a WHERE a_id = 11;