There are quite a few lines of code and logs that may be complicated and difficult to understand. I apologize for that.
The following is OR full-text search for Video.title and Tag.name using pgroonga, an extension for full-text search.
But the query takes about 2000-4000 ms to execute. The number of hits is about 130,000, but we have a LIMIT 20 limit. The index for full-text search is being used successfully.
Video: 300K rows Tag: 5K rows video_tag_through: 1.3M rows
# models.py
class Tag(models.Model):
name = models.CharField(unique=True, max_length=30)
created_at = models.DateTimeField(default=timezone.now)
...
class Video(models.Model):
title = models.CharField(max_length=300)
tags = models.ManyToManyField(Tag, blank=True)
updated_at = models.DateTimeField(auto_now=True)
...
class History(models.Model):
user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
video = models.ForeignKey(Video, on_delete=models.CASCADE)
...
class Favorite(models.Model):
user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE
video = models.ForeignKey(Video, on_delete=models.CASCADE)
...
class Playlist(models.Model):
user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
is_wl = models.BooleanField(default=False, editable=False)
...
class Track(models.Model):
playlist = models.ForeignKey(Playlist, on_delete=models.CASCADE, null=True)
video = models.ForeignKey(Video, on_delete=models.CASCADE)
...
# migration file
...
operations = [
migrations.RunSQL(
"CREATE EXTENSION pgroonga",
"DROP EXTENSION pgroonga",
),
migrations.RunSQL(
"CREATE INDEX pgroonga_video_index ON videos_video USING pgroonga (title pgroonga_varchar_full_text_search_ops_v2)",
"DROP INDEX pgroonga_video_index",
),
migrations.RunSQL(
"CREATE INDEX pgroonga_tag_index ON videos_tag USING pgroonga (name pgroonga_varchar_full_text_search_ops_v2)",
"DROP INDEX pgroonga_tag_index",
),
]
# lookups.py
class Groonga(Lookup):
lookup_name = "groonga"
def as_sql(self, compiler, connection):
lhs, lhs_params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
params = lhs_params + rhs_params
return "%s &@~ %s" % (lhs, rhs), params
# queryset
Video.objects.annotate(
is_viewed=Exists(History.objects.filter(user=user, video=OuterRef("pk"))),
is_favorited=Exists(
Favorite.objects.filter(user=user, video=OuterRef("pk"))
),
is_wl=Exists(
Track.objects.filter(
playlist__user=user, playlist__is_wl=True, video=OuterRef("pk")
)
),
).filter(
Q(title__groonga=value)
| Q(tags__pk__in=Tag.objects.filter(name__groonga=value).values_list("pk")),
is_public=True,
published_at__lte=timezone.now(),
).order_by("-published_at").distinct()[:20]
SELECT DISTINCT "videos_video"."id",
"videos_video"."published_at",
EXISTS
(SELECT (1) AS "a"
FROM "videos_history" U0
WHERE (U0."user_id" IS NULL
AND U0."video_id" = "videos_video"."id")
LIMIT 1) AS "is_viewed",
EXISTS
(SELECT (1) AS "a"
FROM "videos_favorite" U0
WHERE (U0."user_id" IS NULL
AND U0."video_id" = "videos_video"."id")
LIMIT 1) AS "is_favorited",
EXISTS
(SELECT (1) AS "a"
FROM "videos_track" U0
INNER JOIN "videos_playlist" U1 ON (U0."playlist_id" = U1."id")
WHERE (U1."is_wl"
AND U1."user_id" IS NULL
AND U0."video_id" = "videos_video"."id")
LIMIT 1) AS "is_wl"
FROM "videos_video"
LEFT OUTER JOIN "videos_video_tags" ON ("videos_video"."id" = "videos_video_tags"."video_id")
WHERE ("videos_video"."is_public"
AND "videos_video"."published_at" <= '2021-12-27 13:34:29.103369+00:00'
AND ("videos_video"."title" &@~ 'word'
OR "videos_video_tags"."tag_id" IN
(SELECT U0."id"
FROM "videos_tag" U0
WHERE U0."name" &@~ 'word')))
ORDER BY "videos_video"."published_at" DESC
LIMIT 20;
-- QUERY PLAN
-- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Limit (cost=25920044.10..25920044.40 rows=20 width=27) (actual time=8031.477..8083.885 rows=20 loops=1)
-- -> Unique (cost=25920044.10..25930094.65 rows=670037 width=27) (actual time=7498.770..7551.175 rows=20 loops=1)
-- -> Sort (cost=25920044.10..25921719.19 rows=670037 width=27) (actual time=7498.768..7498.796 rows=30 loops=1)
-- Sort Key: videos_video.published_at DESC, videos_video.id, ((hashed SubPlan 2)), ((hashed SubPlan 4)), ((hashed SubPlan 6))
-- Sort Method: external merge Disk: 12232kB
-- -> Hash Right Join (cost=22049.49..25839171.52 rows=670037 width=27) (actual time=258.201..7221.055 rows=336302 loops=1)
-- Hash Cond: (videos_video_tags.video_id = videos_video.id)
-- Filter: ((videos_video.title &@~ 'word'::character varying) OR (hashed SubPlan 7))
-- Rows Removed by Filter: 1002707
-- -> Seq Scan on videos_video_tags (cost=0.00..24544.40 rows=1338740 width=32) (actual time=0.071..321.529 rows=1338740 loops=1)
-- -> Hash (cost=13230.75..13230.75 rows=290059 width=117) (actual time=229.084..229.085 rows=290059 loops=1)
-- Buckets: 32768 Batches: 16 Memory Usage: 2899kB
-- -> Seq Scan on videos_video (cost=0.00..13230.75 rows=290059 width=117) (actual time=0.049..80.893 rows=290059 loops=1)
-- Filter: (is_public AND (published_at <= '2021-12-27 13:34:29.103369+00'::timestamp with time zone))
-- Rows Removed by Filter: 1
-- SubPlan 2
-- -> Bitmap Heap Scan on videos_history u0 (cost=4.18..12.63 rows=4 width=16) (actual time=0.005..0.007 rows=0 loops=1)
-- Recheck Cond: (user_id IS NULL)
-- -> Bitmap Index Scan on videos_history_user_id_9a1343c1 (cost=0.00..4.18 rows=4 width=0) (actual time=0.005..0.005 rows=0 loops=1)
-- Index Cond: (user_id IS NULL)
-- SubPlan 4
-- -> Bitmap Heap Scan on videos_favorite u0_1 (cost=4.19..12.65 rows=5 width=16) (actual time=0.003..0.004 rows=0 loops=1)
-- Recheck Cond: (user_id IS NULL)
-- -> Bitmap Index Scan on videos_favorite_user_id_c4289dec (cost=0.00..4.19 rows=5 width=0) (actual time=0.002..0.003 rows=0 loops=1)
-- Index Cond: (user_id IS NULL)
-- SubPlan 6
-- -> Nested Loop (cost=8.36..23.98 rows=2 width=16) (actual time=0.059..0.061 rows=0 loops=1)
-- -> Bitmap Heap Scan on videos_playlist u1 (cost=4.17..11.27 rows=1 width=16) (actual time=0.058..0.059 rows=0 loops=1)
-- Recheck Cond: (user_id IS NULL)
-- Filter: is_wl
-- -> Bitmap Index Scan on videos_playlist_user_id_e71a2f32 (cost=0.00..4.17 rows=3 width=0) (actual time=0.054..0.054 rows=0 loops=1)
-- Index Cond: (user_id IS NULL)
-- -> Bitmap Heap Scan on videos_track u0_2 (cost=4.19..12.66 rows=5 width=32) (never executed)
-- Recheck Cond: (playlist_id = u1.id)
-- -> Bitmap Index Scan on videos_track_playlist_id_bfcae4d7 (cost=0.00..4.19 rows=5 width=0) (never executed)
-- Index Cond: (playlist_id = u1.id)
-- SubPlan 7
-- -> Bitmap Heap Scan on videos_tag u0_3 (cost=0.00..93.99 rows=6 width=16) (actual time=26.298..26.322 rows=18 loops=1)
-- Recheck Cond: (name &@~ 'word'::character varying)
-- Heap Blocks: exact=11
-- -> Bitmap Index Scan on pgroonga_tag_index (cost=0.00..0.00 rows=56 width=0) (actual time=0.611..0.612 rows=18 loops=1)
-- Index Cond: (name &@~ 'word'::character varying)
-- Planning Time: 3.298 ms
-- JIT:
-- Functions: 75
-- Options: Inlining true, Optimization true, Expressions true, Deforming true
-- Timing: Generation 14.381 ms, Inlining 20.362 ms, Optimization 374.869 ms, Emission 215.556 ms, Total 625.169 ms
-- Execution Time: 8103.397 ms
-- (48 rows)
-- Time: 8108.641 ms (00:08.109)
———- Without annotate ———-
Removing annotate will improve the speed quite a bit of the time and make it almost normal.
Video.objects.filter(
Q(title__groonga=value)
| Q(tags__pk__in=Tag.objects.filter(name__groonga=value).values_list("pk"))
).order_by("-published_at").distinct()[:20]
SELECT DISTINCT "videos_video"."id",
"videos_video"."published_at"
FROM "videos_video"
LEFT OUTER JOIN "videos_video_tags" ON ("videos_video"."id" = "videos_video_tags"."video_id")
WHERE ("videos_video"."is_public"
AND "videos_video"."published_at" <= '2021-12-27 13:34:29.103369+00:00'
AND ("videos_video"."title" &@~ 'word'
OR "videos_video_tags"."tag_id" IN
(SELECT U0."id"
FROM "videos_tag" U0
WHERE U0."name" &@~ 'word')))
ORDER BY "videos_video"."published_at" DESC
LIMIT 20;
-- QUERY PLAN
-- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Limit (cost=1098.41..1151.58 rows=20 width=24) (actual time=53.929..70.971 rows=20 loops=1)
-- -> Unique (cost=1098.41..772131.56 rows=290059 width=24) (actual time=53.928..70.968 rows=20 loops=1)
-- -> Gather Merge (cost=1098.41..768781.38 rows=670037 width=24) (actual time=53.927..70.948 rows=30 loops=1)
-- Workers Planned: 2
-- Workers Launched: 2
-- -> Incremental Sort (cost=98.39..690442.46 rows=279182 width=24) (actual time=4.133..6.338 rows=54 loops=3)
-- Sort Key: videos_video.published_at DESC, videos_video.id
-- Presorted Key: videos_video.published_at
-- Full-sort Groups: 1 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB
-- Worker 0: Full-sort Groups: 2 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB
-- Worker 1: Full-sort Groups: 4 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB
-- -> Nested Loop Left Join (cost=94.86..680403.31 rows=279182 width=24) (actual time=2.516..6.142 rows=79 loops=3)
-- Filter: ((videos_video.title &@~ 'word'::character varying) OR (hashed SubPlan 1))
-- Rows Removed by Filter: 357
-- -> Parallel Index Scan Backward using videos_video_published_at_67cd5ed9 on videos_video (cost=0.42..45317.06 rows=120858 width=117) (actual time=0.021..0.530 rows=124 loops=3)
-- Index Cond: (published_at <= '2021-12-27 13:34:29.103369+00'::timestamp with time zone)
-- Filter: is_public
-- -> Index Only Scan using videos_video_tags_video_id_tag_id_f8d6ba70_uniq on videos_video_tags (cost=0.43..0.68 rows=6 width=32) (actual time=0.008..0.009 rows=4 loops=372)
-- Index Cond: (video_id = videos_video.id)
-- Heap Fetches: 0
-- SubPlan 1
-- -> Bitmap Heap Scan on videos_tag u0 (cost=0.00..93.99 rows=6 width=16) (actual time=1.787..1.818 rows=18 loops=3)
-- Recheck Cond: (name &@~ 'word'::character varying)
-- Heap Blocks: exact=11
-- -> Bitmap Index Scan on pgroonga_tag_index (cost=0.00..0.00 rows=56 width=0) (actual time=1.774..1.774 rows=18 loops=3)
-- Index Cond: (name &@~ 'word'::character varying)
-- Planning Time: 1.507 ms
-- Execution Time: 71.267 ms
-- (28 rows)
-- Time: 73.297 ms
———- Without Q(tags__pk__in=Tag.objects.filter... ———-
If you do not remove annotate, but remove Q(tags__pk__in=Tag.objects.filter... without removing annotate, it will be even faster. I believe that this level of speed is not a problem.
Video.objects.annotate(
is_viewed=Exists(History.objects.filter(user=user, video=OuterRef("pk"))),
is_favorited=Exists(
Favorite.objects.filter(user=user, video=OuterRef("pk"))
),
is_wl=Exists(
Track.objects.filter(
playlist__user=user, playlist__is_wl=True, video=OuterRef("pk")
)
),
).filter(
title__groonga=value,
is_public=True,
published_at__lte=timezone.now(),
).order_by("-published_at")[:20]
SELECT "videos_video"."id",
"videos_video"."published_at",
EXISTS
(SELECT (1) AS "a"
FROM "videos_history" U0
WHERE (U0."user_id" IS NULL
AND U0."video_id" = "videos_video"."id")
LIMIT 1) AS "is_viewed",
EXISTS
(SELECT (1) AS "a"
FROM "videos_favorite" U0
WHERE (U0."user_id" IS NULL
AND U0."video_id" = "videos_video"."id")
LIMIT 1) AS "is_favorited",
EXISTS
(SELECT (1) AS "a"
FROM "videos_track" U0
INNER JOIN "videos_playlist" U1 ON (U0."playlist_id" = U1."id")
WHERE (U1."is_wl"
AND U1."user_id" IS NULL
AND U0."video_id" = "videos_video"."id")
LIMIT 1) AS "is_wl"
FROM "videos_video"
WHERE ("videos_video"."is_public"
AND "videos_video"."published_at" <= '2021-12-27 13:34:29.103369+00:00'
AND "videos_video"."title" &@~ 'word')
ORDER BY "videos_video"."published_at" DESC
LIMIT 20;
-- QUERY PLAN
-- -------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Limit (cost=8429.16..9198.49 rows=20 width=27) (actual time=47.240..47.255 rows=20 loops=1)
-- -> Result (cost=8429.16..19584.47 rows=290 width=27) (actual time=47.238..47.251 rows=20 loops=1)
-- -> Sort (cost=8429.16..8429.88 rows=290 width=24) (actual time=47.157..47.161 rows=20 loops=1)
-- Sort Key: videos_video.published_at DESC
-- Sort Method: top-N heapsort Memory: 27kB
-- -> Bitmap Heap Scan on videos_video (cost=0.07..8421.44 rows=290 width=24) (actual time=19.417..41.198 rows=51005 loops=1)
-- Recheck Cond: (title &@~ 'word'::character varying)
-- Filter: (is_public AND (published_at <= '2021-12-27 13:34:29.103369+00'::timestamp with time zone))
-- Heap Blocks: exact=8836
-- -> Bitmap Index Scan on pgroonga_video_index (cost=0.00..0.00 rows=2901 width=0) (actual time=17.936..17.937 rows=51005 loops=1)
-- Index Cond: (title &@~ 'word'::character varying)
-- SubPlan 2
-- -> Bitmap Heap Scan on videos_history u0 (cost=4.18..12.63 rows=4 width=16) (actual time=0.006..0.006 rows=0 loops=1)
-- Recheck Cond: (user_id IS NULL)
-- -> Bitmap Index Scan on videos_history_user_id_9a1343c1 (cost=0.00..4.18 rows=4 width=0) (actual time=0.005..0.005 rows=0 loops=1)
-- Index Cond: (user_id IS NULL)
-- SubPlan 4
-- -> Bitmap Heap Scan on videos_favorite u0_1 (cost=4.19..12.65 rows=5 width=16) (actual time=0.005..0.005 rows=0 loops=1)
-- Recheck Cond: (user_id IS NULL)
-- -> Bitmap Index Scan on videos_favorite_user_id_c4289dec (cost=0.00..4.19 rows=5 width=0) (actual time=0.002..0.002 rows=0 loops=1)
-- Index Cond: (user_id IS NULL)
-- SubPlan 6
-- -> Nested Loop (cost=8.36..23.98 rows=2 width=16) (actual time=0.006..0.007 rows=0 loops=1)
-- -> Bitmap Heap Scan on videos_playlist u1 (cost=4.17..11.27 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)
-- Recheck Cond: (user_id IS NULL)
-- Filter: is_wl
-- -> Bitmap Index Scan on videos_playlist_user_id_e71a2f32 (cost=0.00..4.17 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=1)
-- Index Cond: (user_id IS NULL)
-- -> Bitmap Heap Scan on videos_track u0_2 (cost=4.19..12.66 rows=5 width=32) (never executed)
-- Recheck Cond: (playlist_id = u1.id)
-- -> Bitmap Index Scan on videos_track_playlist_id_bfcae4d7 (cost=0.00..4.19 rows=5 width=0) (never executed)
-- Index Cond: (playlist_id = u1.id)
-- Planning Time: 1.019 ms
-- Execution Time: 47.930 ms
-- (34 rows)
-- Time: 49.810 ms
From the above results, I think it is not so much that annotate is the problem, but rather that there are so many lines in the m2m through model that it is taking a long time to search.
How can I speed up the process without looping through all the through models?
I have been puzzling over this issue for a long time. I would appreciate any help you can give me. If there is any other information you need, please say so.
Aboutadmin
