Opened 2 years ago

Last modified 19 months ago

#33759 closed Cleanup/optimization

Using subquery to filter a model delete creates sub-optimal SQL — at Version 2

Reported by: Christofer Bertonha Owned by:
Component: Database layer (models, ORM) Version: 4.0
Severity: Normal Keywords:
Cc: Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Christofer Bertonha)

Taking in consideration the app of tutorial for demonstrate the issue.
using postgresql as db.

Executing this following code:

subquery = Comment.objects.filter(created_at__lt=datetime(2022, 6, 1))[:1000]
Comment.objects.filter(id__in=subquery).delete()

Generates the following SQL:

DELETE FROM "comment" WHERE "comment"."id" IN (SELECT V0."id" FROM "comment" V0 WHERE V0."id" IN (SELECT U0."id" FROM "comment" U0 WHERE U0."created_at" < '2022-06-01T00:00:00+00:00'::timestamptz LIMIT 1000))

The inner select V0 is completely unnecessary

If a simple select with the same subquery is executed. It generates expected SQL query.

subquery = Comment.objects.filter(created_at__lt=datetime(2022, 6, 1))[:1000]
comments = list( Comment.objects..filter(id__in=subquery) )

SELECT * FROM "comment" WHERE "comment"."id" IN (SELECT U0."id" FROM "comment" U0 WHERE U0."created_at" < '2022-06-01T00:00:00+00:00'::timestamptz LIMIT 1000),

Change History (2)

comment:1 by Christofer Bertonha, 2 years ago

Description: modified (diff)

comment:2 by Christofer Bertonha, 2 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top