Django Generic Foreign keys - Good or Bad considering the SQL performance?

Question

I have a model A which contains a generic foreign key relation with limit choices to 3 other models(consider them as B, C and D) in the same app. And I know the limitations of generic foreign keys that we can't use filter or get or anyother queryset operations.

So to achieve something like this, A.objects.filter(generic_object__name="foo") I have to filter B, C and D's objects first as queryset, iterate over them and use the generic reverse relation to get the A objects as list(not queryset).

I'm not sure about how it'll affect the SQL performace on database as the querying is not direct.

PS: I need to use the generic foreignkeys, so please suggest for any SQL improvement rather than redesigning of models.

Using Django 1.4.3 and Postgres.

Underestimate

what exactly are "generic" foreign keys? – Dingy

I have used the same setup as described here, docs.djangoproject.com/en/dev/ref/contrib/contenttypes/#id1 – Underestimate

Answer 1

I'd like to quote some words from David Cramer: developer of Disqus, Django commiter

Generic relations are fine. They are not slow, just more difficult to manage in your code base.

I saw many people tell others don't use generic relations because it's slow, but never tell how it's slow.

Andreandrea

That true. But I would agree with the difficult to manage code base – Underestimate

Here's the Source – Maniac

Switching away from generic foreign keys seemed to give me a speedup (sorry, don't have numbers to hand) - and also avoids the problem in OPs question. Interestingly, the source linked above quora.com/What-are-the-best-ways-to-improve-Django-performance has conflicting opinions – Hyperspace

I should note - I switched away from GFKs to avoid the exact problem OP is encountering - hard to do forwards query through the relationship (though, reverse is easy) – Hyperspace

further note - I use a "connection object/table" - am not currently using many-to-many, but probably should be! – Hyperspace

Answer 2

Avoid Django's GenericForeignKey has a good and thorough description of the database design antipatterns involved in generic foreign keys (or "polymorphic associations," as they call them in rails-speak).

As for performance, it takes 3 database queries every time you want to retrieve the related GenericForeignKey resource from your model:

1. SELECT object_id_field, object_id from myapp_a WHERE id=1;
2. SELECT app_label, model FROM django_content_type WHERE id=A.object_type_field;
   • In application code, compute table name model + _ + app_label
3. SELECT A.object_id_field FROM TABLE_NAME;

When people say that Generic Foreign Keys have a performance penalty, they are referencing this query overhead.

There are only a very narrow set of circumstances under which you really want to use generic foreign keys. The above-linked article discusses those, as well.

Scruffy

is there any better way to avoid using generic foreignkey in django? – Cordie

@khadimhusen there's many alternatives. Simplest is to add multiple nullable ForeignKey fields in your model and have the application logic to ensure only one is populated (e.g. override model's save()). – Battiste

Answer 3

Add an index_together Meta option to your model:

class Meta:
    index_together = [('cprofile_id', 'cprofile_type')]

Engage

What exactly does this answer? – Battiste