Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Thomas Güttler):

 Just for the records, we use this pattern until we update to Django 1.11:

 Use case: Select users which have permission "some_app.permission_name"
 and which are in group "special_group"
 {{{
 user_ids_with_group =
 User.objects.filter(groups__in=[special_group]).distinct().values_list('pk',
 flat=True)
 user_ids_with_permission =
 
User.objects.filter(authutils.users_with_perm_q('some_app.permission_name')).distinct().values_list('pk',
 flat=True)
 qs =
 
User.objects.filter(pk__in=set(user_ids_with_group).intersection(user_ids_with_permission)).distinct().order_by('last_name',
 'first_name')


 # authutils
 def users_with_perm_q(perm, is_active=True, is_superuser=True):
 try:
 app_label, codename = perm.split('.')
 except ValueError:
 raise ValueError('Permission name should be in the form
 "app_label.perm_name".')

 permission = Permission.objects.get(codename=codename,
 content_type__app_label=app_label)
 user_q = Q(user_permissions=permission)
 group_q = Q(groups__permissions=permission)
 return Q(is_active=is_active) & (Q(is_superuser=is_superuser) | user_q
 | group_q)
 }}}

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.f692ee062ae8f3382fcfbcb95cf90b44%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Tim Graham):

 Support for `QuerySet.union()`, `intersection()`, `difference()` was added
 in #27718.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.2418d41e4ff407871a86fde154b6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Thomas Güttler):

 Just for the records. We have performance issues on postgres because some
 SQL statements have up to six OUTER joins.

 Union and intersection would be easier to read for me (human being) and
 maybe better to optimize for the postgres query planer.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.85b8cab62c34e90041b935f51acfeb49%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Sven R. Kunze):

 @Tim

 On the one hand, I somehow doubt that it's Django responsibility to do it
 (cf. https://www.postgresql.org/message-id/8ccdd027-f4a0-2e0d-
 1e26-2a403fed8d17%40mail.de ).

 On the other hand, there's already an asymmetry between a single "filter"
 and an "OR filter". The former uses "INNER JOIN", the latter uses "OUTER
 JOIN".

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.2b4b1d885ad221cfcad65adb9c437291%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Tim Graham):

 I haven't worked with the ORM enough to know if changing the query
 operator is feasible.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.709faaeca201fab17323d8a0fe7ae433%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Sven R. Kunze):

 Thanks, Tim. You are right, that {{{OUTER LEFT JOIN}}} is required here.

 However, I might state again that our real problem is the performance
 issue. I simultaneously posted a question on the PostgreSQL mailing list:

 https://www.postgresql.org/message-id/flat/97dbd6f9-a289-94d1-325f-
 997df87c672a%40mail.de#97dbd6f9-a289-94d1-325f-997df87c6...@mail.de

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.37c738f8e2ef2133e7cd8419a03d1f48%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

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


Comment:

 I think [https://gist.github.com/akaariai/a4c1acbfedac0f2cbf3e Ansii's
 write up on join promotion] explains why `LEFT OUTER JOIN` is required
 here.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.230ccfb3ac28660f0a25848ea21c62d3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  new
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Tim Graham):

 FYI, if you're not sure it's a bug, you should
 [wiki:TicketClosingReasons/UseSupportChannels use our support channels]
 before opening a ticket.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.cf6e8e2fa375873ea053f0c7ee406aad%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  new
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Sven R. Kunze):

 Replying to [comment:1 Tim Graham]:
 > Please also provide your models.

 {{{
 class BigTable:
 # a lot of unrelated fields
 }}}
 {{{
 class TableA:
 big_table = models.OneToOneField(BigTable, related_name='...',
 primary_key=True)
 item_id = models.CharField(max_length=1024)
 }}}
 {{{
 class TableB:
 big_table = models.ForeignKey(BigTable, related_name='...')
 item_id = models.CharField(max_length=1024)
 }}}

 > I'm not certain whether or not your proposed changes are correct. If you
 can provide a patch so we can check whether or not it breaks existing
 behavior, it'll be more easily to evaluate the idea.

 Neither are we. Maybe, a {{{UNION}}} could also work. The real issue we
 have is the slow execution of the original query.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.e9a1f02cad8c1c13a756942aa28e8996%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN

[Django]

#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
-+-
 Reporter:  Sven R. Kunze|Owner:  nobody
 Type:   |   Status:  new
  Cleanup/optimization   |
Component:  Database layer   |  Version:  1.8
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * needs_better_patch:   => 0
 * needs_tests:   => 0
 * needs_docs:   => 0


Comment:

 Please also provide your models.

 I'm not certain whether or not your proposed changes are correct. If you
 can provide a patch so we can check whether or not it breaks existing
 behavior, it'll be more easily to evaluate the idea.

 Looking for possibly related tickets, I found #8439.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.9d812a548b8542f0914fbe3e27b9f32a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.