Re: [Django] #33706: Implement subquery annotations as (lateral) joins (was: Implement annotations as (lateral) joins)
Django
-------------------------------------+-------------------------------------
Reporter: Bálint Balina | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
annotate,performance,subquery,postgresql| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* status: new => closed
* resolution: => needsinfo
Comment:
To answer your questions I believe it would be doable to adjust the SQL
compilers to have subquery annotations to rely on lateral joins but I'm
not sure doing it by default on all engines that support it will be
beneficial given the amount of work necessary to achieve it.
Some database engine will optimize away computed expressions with the same
identity if they are specified multiple times. The article you linked
provides no proof that this is actually beneficial in anyway beyond SQL
DRY-ness (no `EXPLAIN` output, no benchmarks) which isn't the ORM's main
focus. The SO article is also barely related to this issue, sure it hacks
into the compiler internals to use a `LATERAL` join but it has nothing to
do with subquery annotation handling.
This ticket tracker is used to manage accepted and confirmed issues, I
suggest you use [https://docs.djangoproject.com/en/4.0/internals/mailing-
lists/#django-developers the developer mailing list] to request feedback
on this feature request. I suggest providing proof about claimed
performance benefits and details about implementation database engines
that Django currently supports to gather positive traction for your
proposal. Without them it's unlikely that work that specialize the way
subquery annotation works and complexify the SQL compiler will be
accepted. Searching this ticket tracker, mailing lists, and Github for
keywords such as `subquery` and `lateral` might also help you gather
context on the subject.
I'll close this issue in the mean time as further discussion and
investigation is required before we can accept this work.
--
Ticket URL: <https://code.djangoproject.com/ticket/33706#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.