search

Chocobozzz / PeerTube

ActivityPub-federated video streaming platform using P2P directly in your web browser
https://joinpeertube.org/
GNU Affero General Public License v3.0
13.07k stars 1.51k forks source link

Slow query upon listing playlists #6386

Open kontrollanten opened 6 months ago

kontrollanten commented 6 months ago

Describe the current behavior

In our postgres logs we see a lot of the following slow queries:

SELECT
       "VideoPlaylistModel".*,
       "OwnerAccount->Actor->Server"."id" AS "OwnerAccount.Actor.Server.id",
       "OwnerAccount->Actor->Server"."host" AS "OwnerAccount.Actor.Server.host",
       "OwnerAccount->Actor->Avatars"."id" AS "OwnerAccount.Actor.Avatars.id",
       "OwnerAccount->Actor->Avatars"."filename" AS "OwnerAccount.Actor.Avatars.filename",
       "OwnerAccount->Actor->Avatars"."height" AS "OwnerAccount.Actor.Avatars.height",
       "OwnerAccount->Actor->Avatars"."width" AS "OwnerAccount.Actor.Avatars.width",
       "OwnerAccount->Actor->Avatars"."fileUrl" AS "OwnerAccount.Actor.Avatars.fileUrl",
       "OwnerAccount->Actor->Avatars"."onDisk" AS "OwnerAccount.Actor.Avatars.onDisk",
       "OwnerAccount->Actor->Avatars"."type" AS "OwnerAccount.Actor.Avatars.type",
       "OwnerAccount->Actor->Avatars"."actorId" AS "OwnerAccount.Actor.Avatars.actorId",
       "OwnerAccount->Actor->Avatars"."createdAt" AS "OwnerAccount.Actor.Avatars.createdAt",
       "OwnerAccount->Actor->Avatars"."updatedAt" AS "OwnerAccount.Actor.Avatars.updatedAt",
       "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id",
       "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host",
       "VideoChannel->Actor->Avatars"."id" AS "VideoChannel.Actor.Avatars.id",
       "VideoChannel->Actor->Avatars"."filename" AS "VideoChannel.Actor.Avatars.filename",
       "VideoChannel->Actor->Avatars"."height" AS "VideoChannel.Actor.Avatars.height",
       "VideoChannel->Actor->Avatars"."width" AS "VideoChannel.Actor.Avatars.width",
       "VideoChannel->Actor->Avatars"."fileUrl" AS "VideoChannel.Actor.Avatars.fileUrl",
       "VideoChannel->Actor->Avatars"."onDisk" AS "VideoChannel.Actor.Avatars.onDisk",
       "VideoChannel->Actor->Avatars"."type" AS "VideoChannel.Actor.Avatars.type",
       "VideoChannel->Actor->Avatars"."actorId" AS "VideoChannel.Actor.Avatars.actorId",
       "VideoChannel->Actor->Avatars"."createdAt" AS "VideoChannel.Actor.Avatars.createdAt",
       "VideoChannel->Actor->Avatars"."updatedAt" AS "VideoChannel.Actor.Avatars.updatedAt",
       "Thumbnail"."id" AS "Thumbnail.id",
       "Thumbnail"."filename" AS "Thumbnail.filename",
       "Thumbnail"."height" AS "Thumbnail.height",
       "Thumbnail"."width" AS "Thumbnail.width",
       "Thumbnail"."type" AS "Thumbnail.type",
       "Thumbnail"."fileUrl" AS "Thumbnail.fileUrl",
       "Thumbnail"."automaticallyGenerated" AS "Thumbnail.automaticallyGenerated",
       "Thumbnail"."onDisk" AS "Thumbnail.onDisk",
       "Thumbnail"."videoId" AS "Thumbnail.videoId",
       "Thumbnail"."videoPlaylistId" AS "Thumbnail.videoPlaylistId",
       "Thumbnail"."createdAt" AS "Thumbnail.createdAt",
       "Thumbnail"."updatedAt" AS "Thumbnail.updatedAt"
FROM (
       SELECT
              "VideoPlaylistModel"."id",
              "VideoPlaylistModel"."name",
              "VideoPlaylistModel"."description",
              "VideoPlaylistModel"."privacy",
              "VideoPlaylistModel"."url",
              "VideoPlaylistModel"."uuid",
              "VideoPlaylistModel"."type",
              "VideoPlaylistModel"."ownerAccountId",
              "VideoPlaylistModel"."videoChannelId",
              "VideoPlaylistModel"."createdAt",
              "VideoPlaylistModel"."updatedAt",
              0 AS similarity,
(
                     SELECT
                            Count("id")
                     FROM
                            "videoPlaylistElement"
                     WHERE
                            "videoPlaylistId" = "VideoPlaylistModel"."id") AS "videosLength",
                     "OwnerAccount"."id" AS "OwnerAccount.id",
                     "OwnerAccount"."name" AS "OwnerAccount.name",
                     "OwnerAccount"."actorId" AS "OwnerAccount.actorId",
                     "OwnerAccount->Actor"."id" AS "OwnerAccount.Actor.id",
                     "OwnerAccount->Actor"."preferredUsername" AS "OwnerAccount.Actor.preferredUsername",
                     "OwnerAccount->Actor"."url" AS "OwnerAccount.Actor.url",
                     "OwnerAccount->Actor"."serverId" AS "OwnerAccount.Actor.serverId",
                     "VideoChannel"."id" AS "VideoChannel.id",
                     "VideoChannel"."name" AS "VideoChannel.name",
                     "VideoChannel"."description" AS "VideoChannel.description",
                     "VideoChannel"."actorId" AS "VideoChannel.actorId",
                     "VideoChannel->Actor"."id" AS "VideoChannel.Actor.id",
                     "VideoChannel->Actor"."preferredUsername" AS "VideoChannel.Actor.preferredUsername",
                     "VideoChannel->Actor"."url" AS "VideoChannel.Actor.url",
                     "VideoChannel->Actor"."serverId" AS "VideoChannel.Actor.serverId"
              FROM
                     "videoPlaylist" AS "VideoPlaylistModel"
                     INNER JOIN "account" AS "OwnerAccount" ON "VideoPlaylistModel"."ownerAccountId" = "OwnerAccount"."id"
                     INNER JOIN "actor" AS "OwnerAccount->Actor" ON "OwnerAccount"."actorId" = "OwnerAccount->Actor"."id"
                            AND ("OwnerAccount->Actor"."serverId" IS NULL
                                   OR "OwnerAccount->Actor"."serverId" IN (
                                          SELECT
                                                 "actor"."serverId"
                                          FROM
                                                 "actorFollow"
                                          INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId"
                                   WHERE
                                          "actorFollow"."actorId" = 1))
                            LEFT OUTER JOIN ("videoChannel" AS "VideoChannel"
                            INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id") ON "VideoPlaylistModel"."videoChannelId" = "VideoChannel"."id"
              WHERE ("VideoPlaylistModel"."privacy" = 1
                     AND "VideoPlaylistModel"."type" = 1
                     AND (
                            SELECT
                                   Count("id")
                            FROM
                                   "videoPlaylistElement"
                            WHERE
                                   "videoPlaylistId" = "VideoPlaylistModel"."id") != 0)
              ORDER BY
                     "similarity" DESC,
                     "VideoPlaylistModel"."id" ASC
              LIMIT 2 offset 58) AS "VideoPlaylistModel"
       LEFT OUTER JOIN "server" AS "OwnerAccount->Actor->Server" ON "OwnerAccount.Actor.serverId" = "OwnerAccount->Actor->Server"."id"
       LEFT OUTER JOIN "actorImage" AS "OwnerAccount->Actor->Avatars" ON "OwnerAccount.Actor.id" = "OwnerAccount->Actor->Avatars"."actorId"
              AND "OwnerAccount->Actor->Avatars"."type" = 1
       LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel.Actor.serverId" = "VideoChannel->Actor->Server"."id"
       LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatars" ON "VideoChannel.Actor.id" = "VideoChannel->Actor->Avatars"."actorId"
              AND "VideoChannel->Actor->Avatars"."type" = 1
       LEFT OUTER JOIN "thumbnail" AS "Thumbnail" ON "VideoPlaylistModel"."id" = "Thumbnail"."videoPlaylistId"
ORDER BY
       "similarity" DESC,
       "VideoPlaylistModel"."id" ASC;

They usually takes ~1 second to run.

Steps to reproduce

No response

Describe the expected behavior

No response

Additional information

Chocobozzz commented 5 months ago

Hi,

Can you provide the output of your sql query with EXPLAIN ANALYZE? Can you also provide logs to see what API request is responsible for this SQL call? Can you also test if the query is slow if you change ORDER BY "similarity" DESC, "VideoPlaylistModel"."id" ASC; to "VideoPlaylistModel"."createdAt" DESC, "VideoPlaylistModel"."id" ASC (in the inner + outer queries)

kontrollanten commented 5 months ago

There's a lot of content in our access logs so hard to pin point which API call is responsible. But I'm pretty sure it's one of the GET /api/v1/search calls. When I scrolled through the pagination on our search page it went slow and new slow logs appeared.

Original query
``` QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------- Sort (cost=528736.38..528736.39 rows=1 width=2138) (actual time=1070.385..1070.395 rows=0 loops=1) Sort Key: (0) DESC, "VideoPlaylistModel".id Sort Method: quicksort Memory: 25kB -> Nested Loop Left Join (cost=396536.12..528736.37 rows=1 width=2138) (actual time=1070.330..1070.339 rows=0 loops=1) -> Nested Loop Left Join (cost=396535.83..528728.07 rows=1 width=1940) (actual time=1070.329..1070.337 rows=0 loops=1) -> Nested Loop Left Join (cost=396535.55..528719.76 rows=1 width=1765) (actual time=1070.328..1070.336 rows=0 loops=1) Join Filter: ("VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server".id) -> Nested Loop Left Join (cost=396535.55..528717.92 rows=1 width=1245) (actual time=1070.328..1070.335 rows=0 loops=1) -> Nested Loop Left Join (cost=396535.27..528709.61 rows=1 width=1070) (actual time=1070.327..1070.334 rows=0 loops=1) Join Filter: ("OwnerAccount->Actor"."serverId" = "OwnerAccount->Actor->Server".id) -> Limit (cost=396535.27..528707.77 rows=1 width=550) (actual time=1070.326..1070.332 rows=0 loops=1) -> Nested Loop Left Join (cost=17.77..396535.27 rows=3 width=550) (actual time=0.483..43.699 rows=50 loops=1) -> Nested Loop (cost=17.19..396415.30 rows=3 width=290) (actual time=0.411..41.608 rows=50 loops=1) -> Nested Loop (cost=0.29..396339.15 rows=4 width=228) (actual time=0.368..40.763 rows=54 loops=1) Join Filter: ("VideoPlaylistModel"."ownerAccountId" = "OwnerAccount".id) Rows Removed by Join Filter: 248386 -> Index Scan using "videoPlaylist_pkey" on "videoPlaylist" "VideoPlaylistModel" (cost=0.29..394642 .64 rows=4 width=210) (actual time=0.271..7.946 rows=54 loops=1) Filter: ((privacy = 1) AND (type = 1) AND ((SubPlan 3) <> 0)) Rows Removed by Filter: 13602 SubPlan 3 -> Aggregate (cost=28.79..28.80 rows=1 width=8) (actual time=0.045..0.045 rows=1 loops=71) -> Index Scan using video_playlist_element_video_playlist_id on "videoPlaylistElement" "videoPlaylistElement_1" (cost=0.29..28.77 rows=7 width=4) (actual time=0.007..0.039 rows=54 loops=71) Index Cond: ("videoPlaylistId" = "VideoPlaylistModel".id) -> Materialize (cost=0.00..542.14 rows=20076 width=18) (actual time=0.000..0.374 rows=4601 loops=54 ) -> Seq Scan on account "OwnerAccount" (cost=0.00..441.76 rows=20076 width=18) (actual time=0. 016..2.127 rows=13465 loops=1) -> Index Scan using actor_pkey on actor "OwnerAccount->Actor" (cost=16.90..19.04 rows=1 width=62) (actual time=0.014..0.015 rows=1 loops=54) Index Cond: (id = "OwnerAccount"."actorId") Filter: (("serverId" IS NULL) OR (hashed SubPlan 2)) Rows Removed by Filter: 0 SubPlan 2 -> Nested Loop (cost=0.57..16.61 rows=1 width=4) (actual time=0.019..0.021 rows=0 loops=1) -> Index Only Scan using actor_follow_actor_id_target_actor_id on "actorFollow" (cost=0.28..8.30 rows=1 width=4) (actual time=0.018..0.019 rows=0 loops=1) Index Cond: ("actorId" = 1) Heap Fetches: 0 -> Index Scan using actor_pkey on actor (cost=0.29..8.31 rows=1 width=8) (never executed) Index Cond: (id = "actorFollow"."targetActorId") -> Nested Loop (cost=0.58..11.18 rows=1 width=248) (actual time=0.009..0.009 rows=1 loops=50) -> Index Scan using "videoChannel_pkey" on "videoChannel" "VideoChannel" (cost=0.29..8.30 rows=1 width=186) (actual time=0.005..0.005 rows=1 loops=50) Index Cond: (id = "VideoPlaylistModel"."videoChannelId") -> Index Scan using actor_pkey on actor "VideoChannel->Actor" (cost=0.29..2.88 rows=1 width=62) (actual time=0.003..0.003 rows=1 loops=50) Index Cond: (id = "VideoChannel"."actorId") SubPlan 1 -> Aggregate (cost=28.79..28.80 rows=1 width=8) (actual time=0.031..0.031 rows=1 loops=50) -> Index Scan using video_playlist_element_video_playlist_id on "videoPlaylistElement" (cost=0.29..28.77 rows=7 width=4) (actual time=0.003..0.025 rows=76 loops=50) Index Cond: ("videoPlaylistId" = "VideoPlaylistModel".id) -> Seq Scan on server "OwnerAccount->Actor->Server" (cost=0.00..1.37 rows=37 width=520) (never executed) -> Index Scan using actor_image_actor_id_type_width on "actorImage" "OwnerAccount->Actor->Avatars" (cost=0.28..8.30 rows=1 width=175) (never executed) Index Cond: (("actorId" = "OwnerAccount->Actor".id) AND (type = 1)) -> Seq Scan on server "VideoChannel->Actor->Server" (cost=0.00..1.37 rows=37 width=520) (never executed) -> Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Actor->Avatars" (cost=0.28..8.30 rows=1 width=175) (never executed) Index Cond: (("actorId" = "VideoChannel->Actor".id) AND (type = 1)) -> Index Scan using thumbnail_video_playlist_id on thumbnail "Thumbnail" (cost=0.28..8.30 rows=1 width=198) (never executed) Index Cond: ("videoPlaylistId" = "VideoPlaylistModel".id) Planning Time: 5.314 ms JIT: Functions: 87 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 9.825 ms, Inlining 71.154 ms, Optimization 588.283 ms, Emission 365.690 ms, Total 1034.952 ms Execution Time: 1102.341 ms (59 rows) ```
With `"VideoPlaylistModel"."createdAt" DESC, "VideoPlaylistModel"."id" ASC`
``` QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join (cost=396027.27..396083.83 rows=1 width=2138) (actual time=256.100..256.114 rows=0 loops=1) -> Nested Loop Left Join (cost=396026.99..396075.53 rows=1 width=1940) (actual time=256.099..256.112 rows=0 loops=1) -> Nested Loop Left Join (cost=396026.71..396067.22 rows=1 width=1765) (actual time=256.098..256.110 rows=0 loops=1) Join Filter: ("VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server".id) -> Nested Loop Left Join (cost=396026.71..396065.39 rows=1 width=1245) (actual time=256.097..256.109 rows=0 loops=1) -> Nested Loop Left Join (cost=396026.42..396057.07 rows=1 width=1070) (actual time=256.096..256.108 rows=0 loops=1) Join Filter: ("OwnerAccount->Actor"."serverId" = "OwnerAccount->Actor->Server".id) -> Limit (cost=396026.42..396055.23 rows=1 width=550) (actual time=256.095..256.106 rows=0 loops=1) -> Result (cost=395940.00..396026.42 rows=3 width=550) (actual time=182.597..184.948 rows=50 loops=1) -> Sort (cost=395940.00..395940.00 rows=3 width=542) (actual time=179.606..179.628 rows=50 loops=1) Sort Key: "VideoPlaylistModel"."createdAt" DESC, "VideoPlaylistModel".id Sort Method: quicksort Memory: 53kB -> Nested Loop Left Join (cost=17.48..395939.97 rows=3 width=542) (actual time=6.570..179.469 rows=50 loops=1) -> Nested Loop (cost=16.90..395906.39 rows=3 width=290) (actual time=6.524..179.076 rows=50 loops=1) -> Nested Loop (cost=0.00..395830.24 rows=4 width=228) (actual time=6.461..178.077 rows=54 loops=1) Join Filter: ("VideoPlaylistModel"."ownerAccountId" = "OwnerAccount".id) Rows Removed by Join Filter: 1076868 -> Seq Scan on account "OwnerAccount" (cost=0.00..441.76 rows=20076 width=18) (actual time=0.015..2.175 rows=19943 loops=1) -> Materialize (cost=0.00..394183.93 rows=4 width=210) (actual time=0.000..0.003 rows=54 loops=19943) -> Seq Scan on "videoPlaylist" "VideoPlaylistModel" (cost=0.00..394183.91 rows=4 width=210) (actual time=0.075..5.170 rows=54 loops=1) Filter: ((privacy = 1) AND (type = 1) AND ((SubPlan 3) <> 0)) Rows Removed by Filter: 13602 SubPlan 3 -> Aggregate (cost=28.79..28.80 rows=1 width=8) (actual time=0.040..0.040 rows=1 loops=71) -> Index Scan using video_playlist_element_video_playlist_id on "videoPlaylistElement" "videoPlaylistElement_1" (cost=0.29..28.77 rows=7 width=4) (actual time=0.005..0.035 rows=54 loops=71) Index Cond: ("videoPlaylistId" = "VideoPlaylistModel".id) -> Index Scan using actor_pkey on actor "OwnerAccount->Actor" (cost=16.90..19.04 rows=1 width=62) (actual time=0.017..0.017 rows=1 loops=54) Index Cond: (id = "OwnerAccount"."actorId") Filter: (("serverId" IS NULL) OR (hashed SubPlan 2)) Rows Removed by Filter: 0 SubPlan 2 -> Nested Loop (cost=0.57..16.61 rows=1 width=4) (actual time=0.016..0.018 rows=0 loops=1) -> Index Only Scan using actor_follow_actor_id_target_actor_id on "actorFollow" (cost=0.28..8.30 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1) Index Cond: ("actorId" = 1) Heap Fetches: 0 -> Index Scan using actor_pkey on actor (cost=0.29..8.31 rows=1 width=8) (never executed) Index Cond: (id = "actorFollow"."targetActorId") -> Nested Loop (cost=0.58..11.18 rows=1 width=248) (actual time=0.007..0.007 rows=1 loops=50) -> Index Scan using "videoChannel_pkey" on "videoChannel" "VideoChannel" (cost=0.29..8.30 rows=1 width=186) (actual time=0.004..0.004 rows=1 loops=50) Index Cond: (id = "VideoPlaylistModel"."videoChannelId") -> Index Scan using actor_pkey on actor "VideoChannel->Actor" (cost=0.29..2.88 rows=1 width=62) (actual time=0.002..0.002 rows=1 loops=50) Index Cond: (id = "VideoChannel"."actorId") SubPlan 1 -> Aggregate (cost=28.79..28.80 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=50) -> Index Scan using video_playlist_element_video_playlist_id on "videoPlaylistElement" (cost=0.29..28.77 rows=7 width=4) (actual time=0.004..0.039 rows=76 loops=50) Index Cond: ("videoPlaylistId" = "VideoPlaylistModel".id) -> Seq Scan on server "OwnerAccount->Actor->Server" (cost=0.00..1.37 rows=37 width=520) (never executed) -> Index Scan using actor_image_actor_id_type_width on "actorImage" "OwnerAccount->Actor->Avatars" (cost=0.28..8.30 rows=1 width=175) (never executed) Index Cond: (("actorId" = "OwnerAccount->Actor".id) AND (type = 1)) -> Seq Scan on server "VideoChannel->Actor->Server" (cost=0.00..1.37 rows=37 width=520) (never executed) -> Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Actor->Avatars" (cost=0.28..8.30 rows=1 width=175) (never executed) Index Cond: (("actorId" = "VideoChannel->Actor".id) AND (type = 1)) -> Index Scan using thumbnail_video_playlist_id on thumbnail "Thumbnail" (cost=0.28..8.30 rows=1 width=198) (never executed) Index Cond: ("videoPlaylistId" = "VideoPlaylistModel".id) Planning Time: 3.750 ms JIT: Functions: 88 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 12.625 ms, Inlining 0.000 ms, Optimization 5.447 ms, Emission 67.502 ms, Total 85.574 ms Execution Time: 268.774 ms (60 rows) ```