Opened 4 months ago

Last modified 4 months ago

#31507 new Cleanup/optimization

Augment QuerySet.exists() optimizations to .union().exists().

Reported by: Simon Charette Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Simon Charette)

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 (2)

comment:1 Changed 4 months ago by Simon Charette

Description: modified (diff)

comment:2 Changed 4 months ago by felixxm

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