#33928: Performance issues with `on_delete=models.SET_NULL` on large tables
-------------------------------------+-------------------------------------
Reporter: Renan | Owner: nobody
GEHAN |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hello,
I have the following models configuration:
- `Parent` model
- `Child` model, with a `parent_id` foreign key to a `Parent` model, set
with `on_delete=models.SET_NULL`
Each `Parent` can have a lot of children, in my case roughly 30k.
I'm starting to encounter performance issues that make my jobs timeout,
because the SQL queries simply timeout.
I've enabled query logging, and noticed something weird (that is certainly
that way on purpose, but I don't understand why).
{{{
# Select the parent
SELECT * FROM "parent" WHERE "parent"."id" = 'parent123';
# Select all children
SELECT * FROM "children" WHERE "children"."parent_id" IN ('parent123');
# Update all children `parent_id` column to `NULL`
UPDATE "children" SET "parent_id" = NULL WHERE "children"."id" IN
('child1', 'child2', 'child3', ..., 'child30000');
# Finally delete the parent
DELETE FROM "parent" WHERE "parent"."id" IN ('parent123');
}}}
I would have expected the update condition to simply be `WHERE
"children"."parent_id" = 'parent123'`, but for some reason it isn't.
In the meantime, I'll switch to `on_delete=models.CASCADE`, which in my
case does the trick, but I was curious about the reason why this happens
in the first place.
Thanks in advance
--
Ticket URL: <https://code.djangoproject.com/ticket/33928>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070182a82044c5-41d2d108-52da-49f1-b1ab-122329c5c3ee-000000%40eu-central-1.amazonses.com.
