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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With