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.
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!
Generally ...
LEFT JOIN
when existence of rows to the right is required anyway.count(*)
is a bit faster when counting things that cannot be null. (Well, technically, the LEFT JOIN
in your query could produce a null, but only one per recipe while >= 2
are needed to qualify.)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)
.