Deletion in the Django ORM

I found something which I wanted to make other Djangonauts aware of. Deletion in the Django ORM (i.e. the QuerySet.delete() method) produces what I would consider to be pathological SQL.

Open up a ./manage.py shell, and set up SQL logging:

>>> import logging
>>> dblog = logging.getLogger('django.db.backends')
>>> dblog.setLevel(logging.DEBUG)
>>> dblog.addHandler(logging.StreamHandler())

Import your model:

>>> from polls.models import Poll
>>> Poll.objects.all()
(0.123) SELECT "polls_poll"."id", ... FROM "polls_poll"; args=()

You should see that all queries are logged to the console. Now attempt to delete all the Poll objects:

>>> Poll.objects.all().delete()
(0.123) SELECT "polls_poll"."id", ... FROM "polls_poll"; args=()
(0.123) DELETE FROM "polls_poll" WHERE "id" IN (1, 2, ...); args=(1, 2, ...)

What you’ll see is that in order to delete records from a table, Django first selects every column of every record, and then adds the primary key of each record as an argument to a (potentially) huge and slow DELETE query. If you perform a large number of deletions, consider switching to raw SQL.