[Django] #31792: Use `EXISTS(SELECT 1 ...)` for subqueries
Django
-----------------------------------------+------------------------
Reporter: w0rp | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/31792>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: w0rp | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* type: Uncategorized => Cleanup/optimization
* stage: Unreviewed => Accepted
Comment:
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`
[https://github.com/django/django/blob/156a2138db20abc89933121e4ff2ee2ce56a173a/django/db/models/sql/query.py#L526-L535
(link)] and `SQLCompiler.has_results`
[https://github.com/django/django/blob/156a2138db20abc89933121e4ff2ee2ce56a173a/django/db/models/sql/compiler.py#L1129-L1130
(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
could path could benefit from optimizations such as #24296.
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:1>
Django
Reporter: w0rp | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* component: Uncategorized => Database layer (models, ORM)
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:2>
Django
Reporter: w0rp | Owner: Akhil
Type: | Gandu
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Akhil Gandu):
* owner: nobody => Akhil Gandu
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:3>
Django
Reporter: w0rp | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Akhil Gandu):
* owner: Akhil Gandu => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:4>
Django
Reporter: w0rp | Owner: Harpreet
Type: | Sharma
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Harpreet Sharma):
* owner: (none) => Harpreet Sharma
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:5>
Django
Reporter: w0rp | Owner: (none)
Type: | Status: new
Cleanup/optimization |
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Harpreet Sharma):
* owner: Harpreet Sharma => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:6>
Django
Reporter: w0rp | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* owner: (none) => Simon Charette
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:3>
Django
Reporter: w0rp | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:4>
Django
Reporter: w0rp | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* version: 3.0 => master
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:5>
Django
Reporter: w0rp | Owner: Simon
Type: | Charette
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"51297a92324976a704279b567ec4f80bb92d7b60" 51297a9]:
{{{
#!CommitTicketReference repository=""
revision="51297a92324976a704279b567ec4f80bb92d7b60"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:6>
Django
Reporter: w0rp | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"45f4282149e13a2c1548a579f60d098e397a33d7" 45f4282]:
{{{
#!CommitTicketReference repository=""
revision="45f4282149e13a2c1548a579f60d098e397a33d7"
Refs #31792 -- Updated SQL example in Exists() docs.
Follow up to 51297a92324976a704279b567ec4f80bb92d7b60.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31792#comment:7>