[Django] #30256: autocomplete_fields cause one or two extra queries for each field wth foreign key or many to many relation
Django
foreign key or many to many relation
-------------------------------------------+------------------------
Reporter: George Tantiras | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 2.1
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------------+------------------------
The following model:
models.py
{{{#!python
class Child3(models.Model):
key = models.ForeignKey(Master, on_delete=models.PROTECT)
child_keys = models.ManyToManyField(Child2)
boolean = models.BooleanField()
}}}
admin.py
{{{#!python
@admin.register(models.Child3)
class Child3Admin(admin.ModelAdmin):
search_fields = ('id', )
autocomplete_fields = ('key', 'child_keys')
# form = forms.Child3Form # Uncomment to enable django-autocomplete-
light
}}}
When visiting the url of instance with id 1:
http://127.0.0.1:8000/admin/inl/child3/1/change/
The queries count varies as follows:
no autocomplete feature: 3 queries
with autocomplete_fields enabled: 6 queries
with django-autocomplete-light enabled: 3 queries
I have not calculated the contentype query that sometimes appears and
sometimes not.
When the above model is used as inline to another model the queries for
each related instance accumulate.
I have uploaded the [https://github.com/raratiru/autobug autobug app ]
which illustrates the above using 4 related models using foreign keys and
many to many fields.
--
Ticket URL: <https://code.djangoproject.com/ticket/30256>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: 2.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Carlton Gibson):
* cc: Johannes Hoppe (added)
* component: Uncategorized => contrib.admin
* type: Uncategorized => Cleanup/optimization
* stage: Unreviewed => Accepted
Comment:
Hi George. Thanks for the report.
Happy to provisionally accept this for an optimization. (I'm reading it as
you having a suggestion in mind?)
(@Joe: cc-ing you for comment at this stage: any thoughts?)
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:1>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: 2.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Carlton Gibson):
Note: happy to close this as `wontfix` if there's nothing we can do...
IIRC `django-autocomplete-light` still makes additional HTTP requests to
fetch the autocomplete data. (No?)
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:2>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: 2.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Johannes Hoppe):
Hi @George,
thanks for reporting the bug. I am afraid I need a little bit information
though. Did you use ~django-debug-toolbar~ by any chance, to identify what
the additional queries are?
Based on the code, I don't see where where the additional queries could
come from. If you could past the queries here, this would help a lot.
Thanks,
Joe
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:3>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Cleanup/optimization |
Component: contrib.admin | Version: 2.1
Severity: Normal | Resolution: needsinfo
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 Carlton Gibson):
* status: new => closed
* resolution: => needsinfo
* stage: Accepted => Unreviewed
Comment:
Thanks Joe! (I'll just move this to `needsinfo` pending a follow-up.)
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:4>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Cleanup/optimization |
Component: contrib.admin | Version: 2.1
Severity: Normal | Resolution: needsinfo
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by George Tantiras):
Hallo, thank you for the response.
It took me some hours to understand why I could not reproduce the above in
the project I uploaded, [https://github.com/raratiru/autobug autobug app
] :
The answer is that the queries are reduced in PostgreSQL and not in
SQLite.
I incorporated the debug toolbar in that project and arranged it to use a
Postgresql database with
[https://github.com/raratiru/autobug/blob/master/autobug/settings.py#L81
those credentials].
I uploaded the `queries*.txt` files which report all the queries from
debug toolbar either with their full traceback or only the queries.
I also uploaded a db.json file with all the database contents in json
format.
Here, I paste the queries for the url
http://127.0.0.1:8000/admin/inl/child3/1/change/.
The url http://127.0.0.1:8000/admin/inl/master/1/change/ has many more
queries because it includes all other models as inlines.
[https://github.com/raratiru/autobug/blob/master/queries_only_django-
autocomplete.txt The queries]:
Those first 4 queries are common for all three occasions (django-
autocomplete, autocomplete-light, no autocomplete at all):
{{{
SELECT "django_session"."session_key", "django_session"."session_data",
"django_session"."expire_date" FROM "django_session" WHERE
("django_session"."expire_date" >
'2019-03-21T15:07:04.528846+00:00'::timestamptz AND
"django_session"."session_key" = 'ku98yfv1ublrcwkqfqtguhw5a886241r')
SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login",
"auth_user"."is_superuser", "auth_user"."username",
"auth_user"."first_name", "auth_user"."last_name", "auth_user"."email",
"auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined"
FROM "auth_user" WHERE "auth_user"."id" = 1
SELECT "inl_child3"."id", "inl_child3"."key_id", "inl_child3"."boolean"
FROM "inl_child3" WHERE "inl_child3"."id" = 1
SELECT "inl_child2"."id", "inl_child2"."key_id", "inl_child2"."boolean",
"inl_child2"."child_key_id" FROM "inl_child2" INNER JOIN
"inl_child3_child_keys" ON ("inl_child2"."id" =
"inl_child3_child_keys"."child2_id") WHERE
"inl_child3_child_keys"."child3_id" = 1
}}}
The following 2 queries are added from django-autocomplete only:
{{{
SELECT "inl_master"."id" FROM "inl_master" WHERE "inl_master"."id" IN (1)
SELECT "inl_child2"."id", "inl_child2"."key_id", "inl_child2"."boolean",
"inl_child2"."child_key_id" FROM "inl_child2" WHERE "inl_child2"."id" IN
(2, 5, 3, 1, 4)
}}}
The complete traceback cam be found in
[https://github.com/raratiru/autobug/blob/master/queries_django-
autocomplete.txt this file].
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:5>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Cleanup/optimization |
Component: contrib.admin | Version: 2.1
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by George Tantiras):
* status: closed => new
* resolution: needsinfo =>
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:6>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: master
Severity: Normal | Resolution:
Keywords: autocomplete | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Carlton Gibson):
* keywords: => autocomplete
* version: 2.1 => master
* stage: Unreviewed => Accepted
Comment:
OK, this is a gnarly one but I'm going to Accept it as a possible
optimization.
The **extra** queries are generated when rendering the `optgroups` for the
widgets.
django-autocomplete-light (DAL) [https://github.com/yourlabs/django-
autocomplete-
light/blob/230c9a119d24ef969a39c4fbd28207714f26ca58/src/dal/widgets.py#L132
excludes unselected self.choices before determining the options to
render], i.e. it only initially renders the selected option.
Where the selected option is **just** the foreign key value that we
already selected for the admin this query can be eliminated.
{{{
SELECT "inl_child2"."id", "inl_child2"."key_id", "inl_child2"."boolean",
"inl_child2"."child_key_id"
FROM "inl_child2" INNER JOIN "inl_child3_child_keys" ON ("inl_child2"."id"
= "inl_child3_child_keys"."child2_id")
WHERE "inl_child3_child_keys"."child3_id" = 1
}}}
Looks to already cover in this case:
{{{
SELECT "inl_child2"."id", "inl_child2"."key_id", "inl_child2"."boolean",
"inl_child2"."child_key_id"
FROM "inl_child2"
WHERE "inl_child2"."id" IN (2)
}}}
(Since the FK points to child2.id = 2 in this case.)
Forcing evaluation of the [https://github.com/yourlabs/django-
autocomplete-
light/blob/230c9a119d24ef969a39c4fbd28207714f26ca58/src/dal/widgets.py#L186
temporarily reduced queryset (here)] causes the query count to match that
observed in Django's own autocomplete, since we apply no such
optimization. (i.e All choices are required in `optgroups()` in Django's
version.)
Thus in theory there's a gain to be had here.
Thanks for the report George. Fancy working on a patch? 🙂
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:7>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: master
Severity: Normal | Resolution:
Keywords: autocomplete | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by George Tantiras):
Carlton, thank you for the feedback!
I would like to try for a patch.
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:8>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: master
Severity: Normal | Resolution:
Keywords: autocomplete | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by George Tantiras):
I cannot reproduce the issue using PostgreSQL-11.5.
Currently, Django Debug Toolbar reports the same number of queries when
using dal3, django-autocomplete or "no autocomplete".
However, there is a difference:
* When using dal3 or "no autocomplete" a query appears (it was not present
when I reported this issue):
{{{
DECLARE "_django_curs_140333492991744_3" NO SCROLL CURSOR WITH HOLD FOR
SELECT ••• FROM "inlines_master" WHERE "inlines_master"."id" IN (1)
}}}
* Django autocomplete reatains its behaviour, it does not produce the
above query rather than a proper SELECT as expected (the reason for
reporting this issue):
{{{
SELECT ••• FROM "inlines_master" WHERE "inlines_master"."id" IN (1)
}}}
Is there something right happening or something wrong?
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:9>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: master
Severity: Normal | Resolution:
Keywords: autocomplete | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Simon Charette):
The server side cursor (`DECLARE CURSOR`) get created when using
`iterator` on PostgreSQL; the SQLite backend doesn't support this feature.
The only internal use of `.iterator()` I can think of is in
`ModelChoiceIterator.choice`
([https://github.com/django/django/blob/2847d2c760438195c4c71ea9d3fded1ce116ea4e/django/forms/models.py#L1128-L1141
link]) if that can be of any help.
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:10>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: master
Severity: Normal | Resolution:
Keywords: autocomplete | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by brettdh):
I think I'm having the same issue described here, but I'm using the
autocomplete functionality built into Django 2. Also, I'm currently on
PostgreSQL 9.6.15.
George, does your last comment apply to Django 2 built-in autocomplete?
i.e. if we upgrade to a later PostgreSQL, would you expect this issue to
go away?
I've also just remembered that we explicitly disable server-side cursors
because we use pgbouncer and encountered this issue some time ago:
https://code.djangoproject.com/ticket/28062 so I'm not sure if that
workaround would work for us (if I'm even understanding it correctly).
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:11>
Django
foreign key or many to many relation
Reporter: George Tantiras | Owner: nobody
Component: contrib.admin | Version: dev
Severity: Normal | Resolution: fixed
Keywords: autocomplete | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by George Tantiras):
* status: new => closed
* resolution: => fixed
Comment:
Currently, checking again with Django-4.2 it seems that everything works
as expected to work.
--
Ticket URL: <https://code.djangoproject.com/ticket/30256#comment:12>