[Django] #34591: Django ORM replace INNER JOIN with STRAIGHT_JOIN

[Django]

#34591: Django ORM replace INNER JOIN with STRAIGHT_JOIN
-------------------------------------+-------------------------------------
Reporter: Cubed | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords: sql join
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Similar to this post here from 9+ years ago:
https://code.djangoproject.com/ticket/22438

We have queries being generated that use "inner join" - which should be
fine, but the SQL engine optimiser is not playing nice with it.
When I swap "inner join" for "straight_join" - thus by-passing the
optimiser - I get the performance I'd expect.
With "inner join" - 1+ hour
With "straight_join" - 2 seconds.

This isnt a fault of Django, but after a lot of researching and tinkering,
it appears sometimes you have to by-pass the optimiser to get the exact
query you want.

Is this possible to do in Django? Perhaps Ive missed it in the docs. Even
if its something a bit hacky like:

qs = myModel.object.filters(....).otherStuff(...)
qs.raw_sql.replace("inner ", "straight_")

that would be enough for me to test this further.

Any pointers would be greatly appreciated.

--
Ticket URL: <https://code.djangoproject.com/ticket/34591>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34591: Django ORM replace INNER JOIN with STRAIGHT_JOIN
-------------------------------------+-------------------------------------

Reporter: dev-c | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid

Keywords: sql join | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* status: new => closed
* resolution: => invalid

Comment:

Hello!

The best place to get answers to your question is using any of the user
support options from [https://docs.djangoproject.com/en/dev/faq/help/#how-
do-i-do-x-why-doesn-t-y-work-where-can-i-go-to-get-help this link].

This issue tracker isn't the right channel for these kind of questions
because it is exclusively devoted to serve the development of Django
itself.

--
Ticket URL: <https://code.djangoproject.com/ticket/34591#comment:1>

[Django]

#34591: Django ORM replace INNER JOIN with STRAIGHT_JOIN
-------------------------------------+-------------------------------------
Reporter: dev-c | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: sql join | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by dev-c):

Thanks for the quick response. Where exactly would you recommend I post
this kind of question?

--
Ticket URL: <https://code.djangoproject.com/ticket/34591#comment:2>

[Django]

#34591: Django ORM replace INNER JOIN with STRAIGHT_JOIN
-------------------------------------+-------------------------------------
Reporter: dev-c | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: sql join | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Natalia Bidart):

I would recommend to post to the Django Forum, specifically in the
[https://forum.djangoproject.com/c/users/using-the-orm/17 Using the ORM]
category. You'll get answers soon!

--
Ticket URL: <https://code.djangoproject.com/ticket/34591#comment:3>