autocomplete_fields cause one or two extra queries for each field wth foreign key or many to many relation

[George Tantiras]

The following model:

models.py

class Child3(models.Model):
    key = models.ForeignKey(Master, on_delete=models.PROTECT)
    child_keys = models.ManyToManyField(Child2)
    boolean = models.BooleanField()

admin.py

@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 ​autobug app which illustrates the above using 4 related models using foreign keys and many to many fields.

[Carlton Gibson]

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?)

[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?)

[Johannes Maron]

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

[Carlton Gibson]

Thanks Joe! (I'll just move this to needsinfo pending a follow-up.)

[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, ​autobug app :

The answer is that the queries are reduced in PostgreSQL and not in SQLite.

Here, I paste the queries for the url ​http://127.0.0.1:8000/admin/inl/child3/1/change/.

​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 can be found in ​this file. Note that the extra queries have a huge traceback record, this is the line of the ​first extra query and its traceback.

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.

There are some additions to my project in order to reproduce easily the above:

• I incorporated the debug toolbar in that project and arranged it to use a Postgresql database with ​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.

[Carlton Gibson]

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) ​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 ​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? 🙂

[George Tantiras]

Carlton, thank you for the feedback!

I would like to try for a patch.

[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?

[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 (​link) if that can be of any help.

[Brett Higgins]

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).

[George Tantiras]

Currently, checking again with Django-4.2 it seems that everything works as expected to work.