Opened 5 years ago
Closed 4 years ago
#31507 closed Cleanup/optimization (fixed)
Augment QuerySet.exists() optimizations to .union().exists().
Description (last modified by ) ¶
The QuerySet.exists
method performs optimization by clearing the select clause, dropping ordering, and limiting the number of results to 1 if possible.
A similar optimization can be applied for combined queries when using QuerySet.union()
as some query planers (e.g. MySQL) are not smart enough to prune changes down into combined queries.
For example, given filtered_authors.union(other_authors).exists()
the currently generated is
SELECT 1 FROM (SELECT * FROM authors WHERE ... ORDER BY ... UNION SELECT * FROM authors WHERE ... ORDER BY ...) LIMIT 1;
But some planers won't be smart enough to realize that both *
and ORDER BY
are not necessary and fetch all matching rows. In order to help them we should generate the following SQL
SELECT 1 FROM (SELECT 1 FROM authors WHERE ... LIMIT 1 UNION SELECT 1 FROM authors WHERE ... LIMIT 1) LIMIT 1;
This can already be done manually through filtered_authors.order_by().values(Value(1))[:1].union(other_authors.order_by().values(Value(1))[:1]).exists()
but that involves a lot of boilerplate.
Note that the optimization is only possible in this form for union
and not for intersection
and difference
since they require both sets to be unaltered.
Change History (9)
comment:1 by , 5 years ago
Description: | modified (diff) |
---|
comment:2 by , 5 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:3 by , 4 years ago
Cc: | added |
---|---|
Has patch: | set |
Owner: | changed from | to
Status: | new → assigned |
comment:4 by , 4 years ago
Needs tests: | set |
---|---|
Patch needs improvement: | set |
comment:5 by , 4 years ago
Needs tests: | unset |
---|---|
Patch needs improvement: | unset |
comment:6 by , 4 years ago
Patch needs improvement: | set |
---|
comment:7 by , 4 years ago
Patch needs improvement: | unset |
---|
comment:8 by , 4 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
PR https://github.com/django/django/pull/13667