Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#31792 closed Cleanup/optimization (fixed)

Use `EXISTS(SELECT 1 ...)` for subqueries

Reported by: w0rp Owned by: Simon Charette
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: 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

If you write a QuerySet call like so in Django...

MyModel.objects.all().exists()

the query run will be like so.

SELECT 1 AS "a" FROM "myapp_mymodel" LIMIT 1;

If you use the Exists() function to filter with a subquery like so...

MyModel.objects.filter(Exists(MyOtherModel.objects.all()))

The subquery will be run like so.

... WHERE EXISTS(SELECT "myapp_myothermodel"."id", ... FROM "myapp_myothermodel");

It would be nice if the queries generated for Exists() used SELECT 1 like .exists() does, where possible. In an app I work on, I have one query in particular that is 15KB in size, but only around 8KB if I apply .annotate(_1=Value(1, output_field=IntegerField())).values_list('_1') to all of the subqueries. That change alone is enough to make my queries much easier to debug.

Change History (7)

comment:1 by Simon Charette, 4 years ago

Triage Stage: UnreviewedAccepted
Type: UncategorizedCleanup/optimization

I could swear there was another ticket about that but I cannot find it.

The logic to clear the column is a bit more complex than what you've described here but it's all detailed in Query.has_results (link) and SQLCompiler.has_results (link) so it could be added to Exists.as_sql.

Ideally the logic would be encapsulated in a Query method so it doesn't have to be duplicated in Query.has_results and Exists.as_sql so both path could benefit from optimizations such as #24296.

Last edited 4 years ago by Simon Charette (previous) (diff)

comment:2 by Mariusz Felisiak, 4 years ago

Component: UncategorizedDatabase layer (models, ORM)

comment:3 by Simon Charette, 4 years ago

Owner: set to Simon Charette
Status: newassigned

comment:4 by Simon Charette, 4 years ago

Has patch: set
Last edited 4 years ago by Mariusz Felisiak (previous) (diff)

comment:5 by Mariusz Felisiak, 4 years ago

Triage Stage: AcceptedReady for checkin
Version: 3.0master

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In 51297a9:

Fixed #31792 -- Made Exists() reuse QuerySet.exists() optimizations.

The latter is already optimized to limit the number of results, avoid
selecting unnecessary fields, and drop ordering if possible without
altering the semantic of the query.

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 45f4282:

Refs #31792 -- Updated SQL example in Exists() docs.

Follow up to 51297a92324976a704279b567ec4f80bb92d7b60.

Note: See TracTickets for help on using tickets.
Back to Top