Opened 4 years ago
Last modified 3 years ago
#31507 closed Cleanup/optimization
Augment QuerySet.exists() optimizations to .union().exists(). — at Initial Version
| Reported by: | Simon Charette | Owned by: | nobody |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | dev |
| Severity: | Normal | Keywords: | |
| Cc: | David Wobrock | 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
The QuerySet.exists method performs optimization by clearing the select clause, dropping ordering, and limit 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.