Comments (2)
Chocobozzz
commented on June 8, 2024
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)
from peertube.
kontrollanten
commented on June 8, 2024
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.
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)
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)
from peertube.
Related Issues (20)
- "Stats" button appears for remote video HOT 1
- Plugin new version notification is always showing the latest plugin version, even for old notifications
- Index plugins/themes on first startup, for better user experience for new admins. HOT 1
- Error on FreeBSD when following instructions on diffing configuration changes HOT 1
- Server crashes with "Error: write EPIPE" HOT 2
- Possibility to increase maxAttempts for s3 uploads
- Peertube won't start： "permission denied for schema public" HOT 1
- Question mark "?" in front of user email in webadmin HOT 5
- OAuth 2FA in Documentation HOT 1
- Dereference error type field in REST API documentation HOT 1
- All transcoding jobs result in an error: Output format mp4 is not available HOT 5
- Peertube doesn't display channels HOT 1
- Declarative plugin management HOT 1
- The configuration file issue after upgraded to v6.0.4 HOT 1
- View count for short videos HOT 5
- Error while exporting user account(s). HOT 2
- Email verified when set by external auth providers
- Video admin overview: missing filter for video with original files
- (please delete) HOT 1
- Can't follow channels on multiple servers from Mastodon.
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from peertube.