Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#17389 closed Bug (invalid)

Filtering by forkeign keys add unnecessary table joins

Reported by: TwelveSixty Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Using a filter on a foreign key, even if you do not use any fields on the foreign model results in an unnecessary join in the SQL query, resulting in lower than optimal performance.

In the example below I perform a simple filter on a model to filter all rows which do not have the foreign key (names have been simplified for clarity). Yet even though I do not use the foreign key model, it still has a LEFT OUTER JOIN in the SQL query.

MyModel.objects.filter(foreignKey__isnull=True).count()
SELECT COUNT(*)
FROM "mymodel"
LEFT OUTER JOIN "foreignmodel" ON ("mymodel"."foreignKey_id" = "foreignmodel"."id")
WHERE "foreignmodel"."id" IS NULL

I have resorted to using an extra where clause manually forcing it to negate the extra JOIN.

MyModel.objects.extra(where=['"foreignKey_id" IS NULL']).count()
SELECT COUNT(*)
FROM "mymodel"
WHERE "foreignKey_id" IS NULL

As you can see this removes the LEFT OUTER JOIN and as a result performs much better (especially if this is used to filter on many foreign keys)

Hopefully I am just incorrectly using the queryset, but I would much rather stray away from using manual SQL clauses if possible.

Thanks.

Change History (3)

comment:1 Changed 3 years ago by TwelveSixty

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

The second I posted this I tried the following and it works perfectly :)

MyModel.objects.exclude(foreignKey__isnull=False).count()

Obviously I need to get used to using excludes as well as filters.

Is this the recommended way?

Last edited 3 years ago by TwelveSixty (previous) (diff)

comment:2 Changed 3 years ago by TwelveSixty

On further investigation on my previous post, this performs a double NOT in the SQL query and severely reduces performance as well :(

comment:3 Changed 3 years ago by akaariai

The double NOT is what you get in the current ORM implementation. I assume you get WHERE NOT (fk_id IS NOT NULL). Correcting this is not trivial, but I think there is some interest in the community to fix this.

As I haven't actually tested anything, and I think this is a known problem, I will leave this as closed.

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