Very slow SQL DELETE query on table with foreign key constraint

Question

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;

I think that they are doing nothing because of the 'NO ACTION' statement — Simon Schüpbach
– Simon Schüpbach, Commented Sep 24, 2015 at 15:06
I don't have add any triggers by my own, but the database add automatically some triggers because of the constraints, I think. — Simon Schüpbach
– Simon Schüpbach, Commented Sep 24, 2015 at 15:14

sstan · Accepted Answer · 2015-09-24 14:49:10Z

6

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.

answered Sep 24, 2015 at 14:49

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Schüpbach Over a year ago

The first point I had take care about. But the second was realy helpful.

Collectives™ on Stack Overflow

Very slow SQL DELETE query on table with foreign key constraint

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related