#32143 closed Cleanup/optimization (fixed)

Use EXISTS to exclude multi-valued relationships

Reported by: Simon Charette Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 3.1
Severity: Normal Keywords:
Cc: 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 current logic that is invoked when excluding a multi-valued relationship (sql.Query.split_exclude) pushes down the filtering criteria in a parent.id NOT IN (SELECT child.parent_id ...) subquery.

These kind of operations can be really hard for some query planners to optimize and also tricky to get right due to how the IN operator treats NULL values which is something we've known for a while.

The NOT EXISTS function should be used instead of NOT IN.

Change History (3)

comment:1 Changed 15 months ago by Mariusz Felisiak

Triage Stage: UnreviewedAccepted

comment:2 Changed 15 months ago by Mariusz Felisiak

Owner: changed from nobody to Simon Charette
Status: newassigned
Triage Stage: AcceptedReady for checkin

comment:3 Changed 15 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In 8593e162:

Fixed #32143 -- Used EXISTS to exclude multi-valued relationships.

As mentioned in the pre-existing split_exclude() docstring EXISTS is
easier to optimize for query planers and circumvents the IN (NULL)
handling issue.

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