Opened 17 months ago

Last modified 3 months ago

#30256 new Cleanup/optimization

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

Reported by: George Tantiras Owned by: nobody
Component: contrib.admin Version: master
Severity: Normal Keywords: autocomplete
Cc: Johannes Hoppe Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (11)

comment:1 Changed 17 months ago by Carlton Gibson

Cc: Johannes Hoppe added
Component: Uncategorizedcontrib.admin
Triage Stage: UnreviewedAccepted
Type: UncategorizedCleanup/optimization

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

comment:2 Changed 17 months ago 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?)

comment:3 Changed 17 months ago 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

comment:4 Changed 17 months ago by Carlton Gibson

Resolution: needsinfo
Status: newclosed
Triage Stage: AcceptedUnreviewed

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

comment:5 Changed 17 months ago 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, 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.
Last edited 17 months ago by George Tantiras (previous) (diff)

comment:6 Changed 15 months ago by George Tantiras

Resolution: needsinfo
Status: closednew

comment:7 Changed 15 months ago by Carlton Gibson

Keywords: autocomplete added
Triage Stage: UnreviewedAccepted
Version: 2.1master

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

comment:8 Changed 15 months ago by George Tantiras

Carlton, thank you for the feedback!

I would like to try for a patch.

comment:9 Changed 10 months ago 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?

comment:10 Changed 10 months ago 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 (link) if that can be of any help.

comment:11 Changed 3 months ago by 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).

Note: See TracTickets for help on using tickets.
Back to Top