CODEWITHSUNDEEP
sqlpostgresqlsql-delete
Simon Schüpbach
Simon Schüpbach

Reputation: 194

Very slow SQL DELETE query on table with foreign key constraint

I have got some trouble with a SQL DELETE query. I work on a database (postgres 9.3) with 2 tables (Parent and Child). The child has a relation to the parent with a foreign key.

Parent Table

CREATE TABLE parent
(
  id bigint NOT NULL,
  ...
  CONSTRAINT parent_pkey PRIMARY KEY (id)
)

Child Table

CREATE TABLE child
(
  id bigint NOT NULL,
  parent_id bigint,
  ...
  CONSTRAINT child_pkey PRIMARY KEY (id),
  CONSTRAINT fk_adc9xan172ilseglcmi1hi0co FOREIGN KEY (parent_id)
      REFERENCES parent (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

I inserted in both tables 200'000 entries without any relation ( Child.parent_id = NULL).

But a DELETE query like below has a duration of more than 20 minutes. And that even without a WHERE conditions.

DELETE FROM Parent;

If I don't add the relation constraints the execution time will be done in 400 ms.

What did I miss?

A workable solution is the example below. But I don't know if this is a good idea. Maybe anyone could tell me a better way to do that.

BEGIN WORK;
ALTER TABLE Parent DISABLE TRIGGER ALL;
DELETE FROM Parent;
ALTER TABLE Parent ENABLE TRIGGER ALL;
COMMIT WORK;

Upvotes: 5

Views: 5217

Answers (1)

sstan
sstan

Reputation: 36513

When you delete from Parent, the Child table needs to be queried by parent_id to ensure that no child row refers to the parent row you are about to delete.

To ensure that the child lookup runs quickly, you need to have an index on your parent_id column in the Child table.

Upvotes: 6

Related Questions