`select_related` and `prefetch_related`

[khanetor]

When I inspected the actual SQL queries being run against DB, I found that for foreignkey and manytomany relationships, we have the n+1 problem.

For example, suppose there are 5 teams, each with 11 members, this query:

query {
  teams {
    edges {
      node {
        members {
          edges {
            node {
              name
            }
          }
        }
      }
    }
  }
}

will result in 1 query for selecting teams, and 5 more queries for selecting members of each team. 6 queries in total.

Normally, we would do a query like this Team.objects.all().prefetch_related('members') to reduce to 2 queries.

I think it would be extremely beneficial if DjangoObjectType can detect special fields:

• ForeignField
• OneToOneField
• OneToManyField
• ManyToManyField
  and apply appropriate prefetch_related and/or select_related when such fields are present in graph queries.

[jaredkwright]

Is anyone currently working on this?

[PeteAndersen]

I noticed in the docs on filtering with django_graphene you can define a resolve_xxxx function that returns a queryset. The example they provide is about filtering, but I think you could apply the select_related and prefetch_related modifications here as well.

from graphene import ObjectType
from graphene_django.filter import DjangoFilterConnectionField
from .models import Teams

class Query(ObjectType):
    teams = DjangoFilterConnectionField(CategoryNode)

    def resolve_teams(self, args, info):
        return Team.objects.all().prefetch_related('members')

http://docs.graphene-python.org/projects/django/en/latest/authorization/#queryset-filtering-on-lists

[mjtamlyn]

I have some code which does some intelligent selective application of database optimisations. This version was written against graphene 0.X and is presented without any kind of guarantee. As and when I miraculously have time to work on it, I'd love to formalise this and get it working in a general case, but I've been saying that for about 9 months now so don't hold out hope.

The core idea is to add prefetch_FOO and optimize_FOO methods to your DjangoObjectType. The code will always do prefetch_related, even on FKs, and that will be applied automatically if applicable. The prefetched qs can be customised with the prefetch_FOO method, and further customised by optimize_FOO methods on the next DjangoObjectType. All optimisations are applied selectively - they won't be applied if the graphql query does not require that data.

Note that it also makes use of https://github.com/photocrowd/django-cursor-pagination

I'm in the middle of trying to do a graphene 1.0 update on a fairly large and complex API which hit a bunch of internals, it remains to be seen how well this approach holds up with graphene 1.0. However, I hope it is a somewhat useful start for anyone wanting to work on this.

https://gist.github.com/mjtamlyn/e8e03d78764552289ea4e2155c554deb

[mjtamlyn]

I've updated the patterns in my gist to make more sense with Graphene 2.0. It also now includes a few additional features including manual post-processing functions and nested fields. This code with a health warning - it definitely makes my API faster because it saves thousands of queries, however I think it may also be the source of some raw python performance issues which is preventing further optimisation of the code. I'm also not yet running it in production, and it is only tested on Python 3.

[tfoxy]

Hi all! I've been working on a library that tries to optimize the query by analyzing the gql query:

https://github.com/tfoxy/graphene-django-optimizer

Feel free to open issues if your use case doesn't work.

[zbyte64]

I think graphene-django-optimizer is something we might want to mention in the docs.

[mvanlonden]

@tfoxy thank you 🙇‍♂️! @zbyte64 mind adding a section in the docs about graphene-django-optimizer

[exisz]

Gj, everyone.

Just a quick question: is there a performance drawback if I simply do a prefetch_related on any FK fields in my model?

Because I just tried graphene-django-optimizer, it seems to be breaking DjangoFilterConnectionField, so I have to customize resolve methods.

It seems both DjangoFilterConnectionField and graphene-django-optimizer offer their own resolvers. Hmm.....
@tfoxy

update:

It kind of did work with DjangoFilterConnectionField if I provide a **kwargs placeholder. In your documentation I guess this was lacking

class Query:
    def resolve_listings(self, info, **kwargs):
        # this will play nice with DFCF

[danpalmer]

@gotexis

Just a quick question: is there a performance drawback if I simply do a prefetch_related on any FK fields in my model?

You'd probably be select_related'ing your FKs on your model – prefetch_related would be for any other models that have an FK to the target model. These will both incur performance hits – select_related expands the number of joins that the database needs to perform, and the amount of data that the database needs to return to the Django app. prefetch_related increases the number of database queries that get made, one additional query for each thing being prefetched.

For small models, with few relations, and little data, this is probably fine to just do, but otherwise you may want to be smarter about when to add these queries, by using something like graphene-django-optimizer, or your own custom solution.

[mohamedrez]

I found this PR on Sailor https://github.com/mirumee/saleor/pull/2983/files,
It might be useful, however is there any plan of having this supported by default ?

I personally tried graphene-django-optimizer and it does not solve all the problems for example:
no way of prefetching when having a nested FK that has a nested reverse FK

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.