Geek Answers Handbook

Django ORM - Accelerated Filter with M2M

I am using Django 1.4, Python 2.7, Ubuntu 14.04 and PostgreSQL 9.3.

I have two models that are linked together by a many-to-many relationship (M2M). I get major filtering performance issues in relation to M2M relationships.

class Meat(models.Model):
    name1 = models.CharField(max_length=200)
    name2 = models.CharField(max_length=200)

class Potato(models.Model):
    bad_meats = models.ManyToManyField(
        Meat, null=True, blank=True, related_name="bad_potatoes")

I connect filters in a set of requests for meat.

potato = Potato.objects.get(pk=12345)
qs = Meat.objects.all()
qs = qs.filter(name1='foo')
qs = qs.filter(name2='bar')
qs = qs.exclude(id__in=potato.bad_meats.all())

Using __insignificantly slows down this filtering process. Is there any other approach anyone can suggest to speed this up?

I cannot use the query .raw()because I need to bind filters during this process depending on certain conditions.

, Meat 150 000 , potato.bad_meats.all() - 40 000 . 8-10 . , 1 .

+4
1

150K 40K , 8 , , cpu?

, /:

  • db?
  • name1 name2?
  • , python, , , . values values_list dict/list Model.

db, ( ). db 150 . 1 , 40 . . , , 1 2.3Gz.

,

>>> qs = Meat.objects.filter(name1='foo').filter(name2='bar').exclude(id__in=potato.bad_meats.all())
>>> print qs.query
SELECT "coucou_meat"."id", "coucou_meat"."name1", "coucou_meat"."name2" 
FROM "coucou_meat" 
WHERE ("coucou_meat"."name1" = foo  AND "coucou_meat"."name2" = bar 
AND NOT ("coucou_meat"."id" IN
    (SELECT U0."id" 
    FROM "coucou_meat" U0 
    INNER JOIN "coucou_potato_bad_meats" U1 ON (U0."id" = U1."meat_id") 
    WHERE U1."potato_id" = 1 )))

pgadmin, 325 .

:

>>> qs = Meat.objects.filter(name1='foo').filter(name2='bar').exclude(bad_potatoes__id=12345)
>>> print qs.query
SELECT "coucou_meat"."id", "coucou_meat"."name1", "coucou_meat"."name2" 
FROM "coucou_meat" WHERE ("coucou_meat"."name1" = foo  AND "coucou_meat"."name2" = bar  
AND NOT (("coucou_meat"."id" IN 
    (SELECT U1."meat_id" 
    FROM "coucou_potato_bad_meats" U1 
    WHERE (U1."potato_id" = 1  AND U1."meat_id" IS NOT NULL)) 
        AND "coucou_meat"."id" IS NOT NULL)))

pgadmin 230 . , , , .

+3

More articles:


All Articles