djangopostgresqlindexingmany-to-manypostgresql-performance

Optimizing query to filter many-to-many relationship on row-count


My model:

class RecipeTag(models.Model):
    tag = models.CharField(max_length=300, blank=True, null=True, default=None, db_index=True)
    recipes = models.ManyToManyField(Recipe, blank=True)
    description = models.TextField(blank=True, null=True, default=None)
    language_code = models.CharField(
        max_length=5,
        choices=[(lang[0], lang[1]) for lang in settings.LANGUAGES],
        default='en',
        db_index=True  # Index added here
    )
    slug = models.CharField(max_length=300, blank=True, null=True, default=None, db_index=True)  # Index added here
    is_visible = models.BooleanField(blank=True, null=True, default=True, db_index=True)  # Index added here

    def save(self, *args, **kwargs):
        if not self.slug:
            self.slug = slugify(self.tag)
        super(NewRecipeTag, self).save(*args, **kwargs)

    def __str__(self):
        tag = self.tag if self.tag else "Tag"
        return f"{tag} - Visible - {self.is_visible}"

I need to filter all RecipeTag that have more then 2 recipes assigned to them, that I am currently doing like that

tags = NewRecipeTag.objects.filter(language_code=meta['language'],
    is_visible=True).annotate(num_recipes=Count('recipes')).filter(num_recipes__gte=2)

Then I paginate results like that to display:

paginator = Paginator(recipe_tags, 60)
page = paginator.page(page_number)
recipe_tags = page.object_list

The query takes around 20 seconds. Without annotate it happens almost instantly. I have 4391166 rows and hope to find a faster solution to display paginated tags filtered by recipe-count.


UPDATE

Actual model name is NewRecipeTag, but never mind.
Here is Recipe Model:

class Recipe(models.Model):
    author = models.ForeignKey(CustomUser, blank=True, null=True, on_delete=models.CASCADE, related_name='author', default=None)
    category = models.ForeignKey(Category, related_name="dish_category", blank=True, null=True, on_delete=models.SET_DEFAULT, default=None)
    level = models.ForeignKey(Category, related_name="category_level", blank=True, null=True, on_delete=models.SET_DEFAULT, default=None)
    time = models.ForeignKey(Category, related_name="time", blank=True, null=True, on_delete=models.SET_DEFAULT, default=None)
    website = models.ForeignKey(Website, blank=True, null=True, default=None, on_delete=models.CASCADE, related_name='recipes')
    language = models.CharField(
        max_length=5,
        choices=[('en', 'en'), ('uk', 'uk')],
        default='uk'
    )
    name = models.CharField(max_length=250, blank=True, null=True, default="")
    description = models.TextField(blank=True, null=True, default="")
    image = models.TextField(blank=True, null=True, default=None)
    filed_image = models.ImageField(blank=True, null=True, default=None, upload_to="recipes_photos/")
    url = models.URLField(blank=True, null=True, default="")
    created_at = models.DateTimeField(blank=True, null=True, default=timezone.now)
    updated_at = models.DateTimeField(auto_now=True)
    views = models.IntegerField(blank=True, null=True, default=0)
    slug = models.CharField(max_length=300, blank=True, null=True, default=None)
    sitemap_loc_pk = models.IntegerField(blank=True, null=True, default=None)
    is_active = models.BooleanField(blank=True, null=True, default=True)
    kitchen = models.ForeignKey(Category, related_name="kitchen", blank=True, null=True, on_delete=models.SET_DEFAULT, default=None)
    cook_methods = models.ForeignKey(Category, related_name="cook_methods", blank=True, null=True, on_delete=models.SET_DEFAULT, default=None)
    vegetarian = models.BooleanField(blank=True, null=True, default=False)
    hot = models.BooleanField(blank=True, null=True, default=False)
    dietary = models.ForeignKey(Category, related_name="category_dietary", blank=True, null=True,
                                  on_delete=models.SET_DEFAULT, default=None)

    type_of_food = models.ForeignKey(Category, related_name="category_type_of_food", blank=True, null=True,
                                  on_delete=models.SET_DEFAULT, default=None)

    season = models.ForeignKey(Category, related_name="category_season", blank=True, null=True,
                                  on_delete=models.SET_DEFAULT, default=None)

    food_group = models.ForeignKey(Category, related_name="category_food_group", blank=True, null=True,
                               on_delete=models.SET_DEFAULT, default=None)
    new_tags_generated = models.BooleanField(blank=True, null=True, default=False)

Query:

SELECT "recipe_newrecipetag"."id", "recipe_newrecipetag"."tag", "recipe_newrecipetag"."description", "recipe_newrecipetag"."seo_h1", "recipe_newrecipetag"."seo_title", "recipe_newrecipetag"."seo_description", "recipe_newrecipetag"."seo_text", "recipe_newrecipetag"."language_code", "recipe_newrecipetag"."slug", "recipe_newrecipetag"."is_visible", "recipe_newrecipetag"."sitemap_loc_pk", COUNT("recipe_newrecipetag_recipes"."recipe_id") AS "num_recipes"
FROM "recipe_newrecipetag" LEFT OUTER JOIN "recipe_newrecipetag_recipes" ON ("recipe_newrecipetag"."id" = "recipe_newrecipetag_recipes"."newrecipetag_id")
WHERE ("recipe_newrecipetag"."is_visible" AND "recipe_newrecipetag"."language_code" = uk)
GROUP BY "recipe_newrecipetag"."id"
HAVING COUNT("recipe_newrecipetag_recipes"."recipe_id") >= 2
ORDER BY "recipe_newrecipetag"."tag" ASC

EXPLAIN output:

Sort  (cost=3706458.92..3710089.69 rows=1452305 width=1555)
  Sort Key: recipe_newrecipetag.tag
  ->  GroupAggregate  (cost=4576.12..609237.64 rows=1452305 width=1555)
        Group Key: recipe_newrecipetag.id
        Filter: (count(recipe_newrecipetag_recipes.recipe_id) >= 2)
        ->  Merge Left Join  (cost=4576.12..525178.10 rows=5919621 width=1555)
              Merge Cond: (recipe_newrecipetag.id = recipe_newrecipetag_recipes.newrecipetag_id)
              ->  Index Scan using recipe_newrecipetag_pkey on recipe_newrecipetag  (cost=0.43..228510.04 rows=4356915 width=1547)
                    Filter: (is_visible AND ((language_code)::text = 'uk'::text))
              ->  Index Only Scan using recipe_newrecipetag_reci_newrecipetag_id_recipe_i_87147e82_uniq on recipe_newrecipetag_recipes  (cost=0.43..211664.16 rows=5966157 width=16)

Currently I am thinking about caching requests. But if there is a better option, I will go with that!


Solution

  • Generally ...

    See:

    Basically ...

    If most rows pass is_visible AND language_code = uk:

    SELECT t.id, t.tag, t.description, t.seo_h1, t.seo_title, t.seo_description, t.seo_text. t.language_code, t.slug, t.is_visible, t.sitemap_loc_pk
         , r.num_recipes
    FROM   recipe_newrecipetag t
    JOIN  (
       SELECT newrecipetag_id, count(*) AS num_recipes
       FROM   recipe_newrecipetag_recipes
       GROUP  BY 1
       HAVING count(*) > 1
       ) r ON r.newrecipetag_id = t.id
    WHERE  t.is_visible
    AND    t.language_code = uk
    ORDER  BY t.tag;
    

    Indexes won't help (much).

    If few rows pass:

    SELECT t.id, t.tag, t.description, t.seo_h1, t.seo_title, t.seo_description, t.seo_text. t.language_code, t.slug, t.is_visible, t.sitemap_loc_pk
         , r.num_recipes
    FROM   recipe_newrecipetag t
    CROSS  JOIN LATERAL  (
       SELECT newrecipetag_id, count(*) AS num_recipes
       FROM   recipe_newrecipetag_recipes r
       WHERE  r.newrecipetag_id = t.id
       GROUP  BY 1
       HAVING count(*) > 1
       ) r 
    WHERE  t.is_visible
    AND    t.language_code = uk
    ORDER  BY t.tag;
    

    Indexing can help a lot. Best index setup depends on selectivity of the filter clauses, and the complete picture. Minimum setup: indexes on recipe_newrecipetag(language_code) and recipe_newrecipetag_recipes(newrecipetag_id).