Opened 5 years ago

Closed 4 years ago

#31507 closed Cleanup/optimization (fixed)

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

Reported by: Simon Charette Owned by: David Wobrock
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 (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 (9)

comment:1 by Simon Charette, 5 years ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 5 years ago

Triage Stage: UnreviewedAccepted

comment:3 by David Wobrock, 4 years ago

Cc: David Wobrock added
Has patch: set
Owner: changed from nobody to David Wobrock
Status: newassigned

comment:4 by David Wobrock, 4 years ago

Needs tests: set
Patch needs improvement: set

comment:5 by David Wobrock, 4 years ago

Needs tests: unset
Patch needs improvement: unset

comment:6 by Mariusz Felisiak, 4 years ago

Patch needs improvement: set

comment:7 by David Wobrock, 4 years ago

Patch needs improvement: unset

comment:8 by Mariusz Felisiak, 4 years ago

Triage Stage: AcceptedReady for checkin

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

Resolution: fixed
Status: assignedclosed

In ba42569d:

Fixed #31507 -- Added QuerySet.exists() optimizations to compound queries.

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