Lenteur sur différentes pages

Coucou les gens,

J’ai des lenteurs sur mon instance de taf Peertube et je sais pas trop par quel bout prendre l’analyse, la recherche du pourquoi ?

Il s’agit de Graptube ici pour info.
Autant par ordi que par mobile mais très visible par mobile où les vidéos d’une chaîne ou de la page d’accueil peuvent mettre quasi 10secondes à apparaître

Je connais pas techniquement Peertube mais j’ai un profil technique donc pas de soucis si faut investiguer

merci de votre aide :slight_smile:

Hi, maybe there are problems with the network?
What do the log say?

Houla oui effectivement.

Y’a des choses dans les logs de peertube ? Peut-être activer le slow log de postgresql aussi

J’ai mis les slow log à 1seconde tu me diras si c’est pas pertinent

ça s’active à des selects que voici :

2026-03-02 09:51:57.247 CET [3764159] peertube@peertube_prod LOG:  duration: 5057.927 ms  statement: SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."accountId" AS "VideoChannel.accountId", "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", "VideoChannel->Actor"."accountId" AS "VideoChannel.Actor.accountId", "VideoChannel->Actor"."videoChannelId" AS "VideoChannel.Actor.videoChannelId", "VideoChannel->Actor->Avatars"."id" AS "VideoChannel.Actor.Avatars.id", "VideoChannel->Actor->Avatars"."width" AS "VideoChannel.Actor.Avatars.width", "VideoChannel->Actor->Avatars"."filename" AS "VideoChannel.Actor.Avatars.filename", "VideoChannel->Actor->Avatars"."type" AS "VideoChannel.Actor.Avatars.type", "VideoChannel->Actor->Avatars"."fileUrl" AS "VideoChannel.Actor.Avatars.fileUrl", "VideoChannel->Actor->Avatars"."onDisk" AS "VideoChannel.Actor.Avatars.onDisk", "VideoChannel->Actor->Avatars"."createdAt" AS "VideoChannel.Actor.Avatars.createdAt", "VideoChannel->Actor->Avatars"."updatedAt" AS "VideoChannel.Actor.Avatars.updatedAt", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."accountId" AS "VideoChannel.Account.Actor.accountId", "VideoChannel->Account->Actor"."videoChannelId" AS "VideoChannel.Account.Actor.videoChannelId", "VideoChannel->Account->Actor->Avatars"."id" AS "VideoChannel.Account.Actor.Avatars.id", "VideoChannel->Account->Actor->Avatars"."width" AS "VideoChannel.Account.Actor.Avatars.width", "VideoChannel->Account->Actor->Avatars"."filename" AS "VideoChannel.Account.Actor.Avatars.filename", "VideoChannel->Account->Actor->Avatars"."type" AS "VideoChannel.Account.Actor.Avatars.type", "VideoChannel->Account->Actor->Avatars"."fileUrl" AS "VideoChannel.Account.Actor.Avatars.fileUrl", "VideoChannel->Account->Actor->Avatars"."onDisk" AS "VideoChannel.Account.Actor.Avatars.onDisk", "VideoChannel->Account->Actor->Avatars"."createdAt" AS "VideoChannel.Account.Actor.Avatars.createdAt", "VideoChannel->Account->Actor->Avatars"."updatedAt" AS "VideoChannel.Account.Actor.Avatars.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime" FROM (SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" INNER JOIN "actor" "accountActor" ON "account"."id" = "accountActor"."accountId" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '18')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '18')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false)) AND "videoChannel"."id" = 14 AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND "vifZ6qeWWQXvk0m3s8ow==\""


2026-03-02 09:51:39.174 CET [3763636] peertube@peertube_prod LOG:  duration: 5480.711 ms  statement: SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."accountId" AS "VideoChannel.accountId", "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", "VideoChannel->Actor"."accountId" AS "VideoChannel.Actor.accountId", "VideoChannel->Actor"."videoChannelId" AS "VideoChannel.Actor.videoChannelId", "VideoChannel->Actor->Avatars"."id" AS "VideoChannel.Actor.Avatars.id", "VideoChannel->Actor->Avatars"."width" AS "VideoChannel.Actor.Avatars.width", "VideoChannel->Actor->Avatars"."filename" AS "VideoChannel.Actor.Avatars.filename", "VideoChannel->Actor->Avatars"."type" AS "VideoChannel.Actor.Avatars.type", "VideoChannel->Actor->Avatars"."fileUrl" AS "VideoChannel.Actor.Avatars.fileUrl", "VideoChannel->Actor->Avatars"."onDisk" AS "VideoChannel.Actor.Avatars.onDisk", "VideoChannel->Actor->Avatars"."createdAt" AS "VideoChannel.Actor.Avatars.createdAt", "VideoChannel->Actor->Avatars"."updatedAt" AS "VideoChannel.Actor.Avatars.updatedAt", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."accountId" AS "VideoChannel.Account.Actor.accountId", "VideoChannel->Account->Actor"."videoChannelId" AS "VideoChannel.Account.Actor.videoChannelId", "VideoChannel->Account->Actor->Avatars"."id" AS "VideoChannel.Account.Actor.Avatars.id", "VideoChannel->Account->Actor->Avatars"."width" AS "VideoChannel.Account.Actor.Avatars.width", "VideoChannel->Account->Actor->Avatars"."filename" AS "VideoChannel.Account.Actor.Avatars.filename", "VideoChannel->Account->Actor->Avatars"."type" AS "VideoChannel.Account.Actor.Avatars.type", "VideoChannel->Account->Actor->Avatars"."fileUrl" AS "VideoChannel.Account.Actor.Avatars.fileUrl", "VideoChannel->Account->Actor->Avatars"."onDisk" AS "VideoChannel.Account.Actor.Avatars.onDisk", "VideoChannel->Account->Actor->Avatars"."createdAt" AS "VideoChannel.Account.Actor.Avatars.createdAt", "VideoChannel->Account->Actor->Avatars"."updatedAt" AS "VideoChannel.Account.Actor.Avatars.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime", "VideoLive"."id" AS "VideoLive.id", "VideoLive"."streamKey" AS "VideoLive.streamKey", "VideoLive"."saveReplay" AS "VideoLive.saveReplay", "VideoLive"."permanentLive" AS "VideoLive.permanentLive", "VideoLive"."latencyMode" AS "VideoLive.latencyMode", "VideoLive"."videoId" AS "VideoLive.videoId", "VideoLive"."replaySettingId" AS "VideoLive.replaySettingId", "VideoLive"."createdAt" AS "VideoLive.createdAt", "VideoLive"."updatedAt" AS "VideoLive.updatedAt", "VideoLive->VideoLiveSchedules"."id" AS "VideoLive.VideoLiveSchedules.id", "VideoLive->VideoLiveSchedules"."startAt" AS "VideoLive.VideoLiveSchedules.startAt", "VideoLive->VideoLiveSchedules"."createdAt" AS "VideoLive.VideoLiveSchedules.createdAt", "VideoLive->VideoLiveSchedules"."updatedAt" AS "VideoLive.VideoLiveSchedules.updatedAt" FROM (SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" INNER JOIN "actor" "accountActor" ON "account"."id" = "accountActor"."accountId" LEFT JOIN "videoLive" ON "video"."id" = "videoLive"."videoId" LEFT JOIN "videoLiveSchedule" ON "videoLiveSchedule"."liveVideoId" = "videoLive"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '18')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '18')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false) OR ("video"."state" = 4 AND "videoLiveSchedule"."startAt" > NOW())) AND "videoChannel"."id" = 10 AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND "video"."nsfw" IS FALSE   ORDER BY "video"."publishedAt" DESC, "video"."id" ASC LIMIT 25 OFFSET 25) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."id" = "VideoChannel->Actor"."videoChannelId" 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 INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."id" = "VideoChannel->Account->Actor"."accountId" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatars" ON "VideoChannel->Account->Actor"."id" = "VideoChannel->Account->Actor->Avatars"."actorId" AND "VideoChannel->Account->Actor->Avatars"."type" = 1 LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 3 LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId" LEFT OUTER JOIN "videoLiveSchedule" AS "VideoLive->VideoLiveSchedules" ON "VideoLive->VideoLiveSchedules"."liveVideoId" = "VideoLive"."id"  ORDER BY "video"."publishedAt" DESC, "video"."id" ASC

Ya cette erreur dans les logs (que j’avais pas vu jvais aller voir sur github si ya des soucis similaires)

Mar 02 09:53:07 grp-service-01 peertube[3632906]: [tube.grap.coop:443] 2026-03-02 09:53:07.243 info: Cannot authenticate. {
Mar 02 09:53:07 grp-service-01 peertube[3632906]:   "err": {
Mar 02 09:53:07 grp-service-01 peertube[3632906]:     "stack": "invalid_token: Invalid token: access token is invalid\n    at AuthenticateHandler.getAccessToken (/var/www/peertube/versions/peertube-v8.0.2/node_modules/.pnpm/@node-oauth+oauth2-server@5.2.1/node_modules/@node-oauth/oauth2-server/lib/handlers/authenticate-handler.js:203:13)\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n    at async AuthenticateHandler.handle (/var/www/peertube/versions/peertube-v8.0.2/node_modules/.pnpm/@node-oauth+oauth2-server@5.2.1/node_modules/@node-oauth/oauth2-server/lib/handlers/authenticate-handler.js:70:21)",
Mar 02 09:53:07 grp-service-01 peertube[3632906]:     "message": "Invalid token: access token is invalid",
Mar 02 09:53:07 grp-service-01 peertube[3632906]:     "statusCode": 401,
Mar 02 09:53:07 grp-service-01 peertube[3632906]:     "status": 401,
Mar 02 09:53:07 grp-service-01 peertube[3632906]:     "code": 401,
Mar 02 09:53:07 grp-service-01 peertube[3632906]:     "name": "invalid_token"
Mar 02 09:53:07 grp-service-01 peertube[3632906]:   }
Mar 02 09:53:07 grp-service-01 peertube[3632906]: }

et des erreurs liées au fediverse ?

Mar 02 09:57:03 grp-service-01 peertube[3632906]: [tube.grap.coop:443] 2026-03-02 09:57:03.665 info: Cannot fetch remote actor count https://mastodon.social/ap/users/115705017780116558/following. {
Mar 02 09:57:03 grp-service-01 peertube[3632906]:   "err": {
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "stack": "HTTPError: Request failed with status code 410 (Gone): GET https://mastodon.social/ap/users/115705017780116558/following\n    at Request.<anonymous> (file:///var/www/peertube/versions/peertube-v8.0.2/node_modules/.pnpm/got@14.6.3/node_modules/got/dist/source/as-promise/index.js:98:42)\n    at Object.onceWrapper (node:events:639:26)\n    at Request.emit (node:events:536:35)\n    at Request._onResponseBase (file:///var/www/peertube/versions/peertube-v8.0.2/node_modules/.pnpm/got@14.6.3/node_modules/got/dist/source/core/index.js:781:22)\n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n    at async Request._onResponse (file:///var/www/peertube/versions/peertube-v8.0.2/node_modules/.pnpm/got@14.6.3/node_modules/got/dist/source/core/index.js:831:13)",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "message": "Request failed with status code 410 (Gone): GET https://mastodon.social/ap/users/115705017780116558/following",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "name": "HTTPError",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "responseBody": {
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "error": "Gone"
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     },
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "responseHeaders": {
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "connection": "close",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "content-length": "16",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-robots-tag": "noindex, nofollow",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "referrer-policy": "strict-origin-when-cross-origin",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-origin-robots-tag": "noindex, nofollow",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "server": "Mastodon",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-xss-protection": "0",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-runtime": "0.023826",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "content-type": "application/json; charset=utf-8",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "content-security-policy": "base-uri 'none'; default-src 'none'; frame-ancestors 'none'; font-src 'self' https://mastodon.social; img-src 'self' data: blob: https://mastodon.social https://files.mastodon.social; media-src 'self' data: https://mastodon.social https://files.mastodon.social; manifest-src 'self' https://mastodon.social; form-action 'none'; child-src 'self' blob: https://mastodon.social; worker-src 'self' blob: https://mastodon.social; connect-src 'self' data: blob: https://mastodon.social https://files.mastodon.social wss://streaming.mastodon.social; script-src 'self' https://mastodon.social 'wasm-unsafe-eval'; frame-src 'self' https:; style-src 'self' https://mastodon.social 'nonce-wkddZOvpe1LE1K5g7sLGrw=='",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-frame-options": "DENY",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-content-type-options": "nosniff",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "cache-control": "max-age=180, public",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-request-id": "154ee1b5-0671-4676-9e3e-65b5090e97a5",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "via": "1.1 varnish, 1.1 varnish",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "accept-ranges": "bytes",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "date": "Mon, 02 Mar 2026 08:57:03 GMT",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "age": "56",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-served-by": "cache-fra-eddf8230104-FRA, cache-fra-eddf8230127-FRA, cache-par-lfpg1960076-PAR",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-cache": "MISS, HIT, HIT",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-cache-hits": "0, 6, 1",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "x-timer": "S1772441824.661416,VS0,VE1",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "vary": "Accept, Accept-Language, Cookie",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "strict-transport-security": "max-age=31557600",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "alt-svc": "h3=\":443\";ma=86400,h3-29=\":443\";ma=86400,h3-27=\":443\";ma=86400"
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     },
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "statusCode": 410,
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "requestHeaders": {
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "user-agent": "PeerTube/8.0.2 (+https://tube.grap.coop)",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "date": "Mon, 02 Mar 2026 08:57:03 GMT",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "accept": "application/activity+json, application/ld+json",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "accept-encoding": "gzip, deflate, br",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "host": "mastodon.social",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:       "signature": "keyId=\"https://tube.grap.coop/accounts/peertube#main-key\",algorithm=\"rsa-sha256\",headers=\"(request-target) host date\",signature=\"STictGVny4TmVluL7dNjd7nY4b/bXmG/7yPyGOuljmki7sTTkEJssdXspawCVrIZ3AiRJOPy/1nXpFb6y4uxD6gBvjYslIgKl7LF8YExG2vawk8gUj3zRlNZ8UbLTUdPd2Al0pFmKdO2gud3OTwpOED7A2n9YxcrgzaiWVxILE/gJ4AKq4jJeBF5qCwK4SANFxd8Y4oCO2nUsUKSOBNY5Ksq0/XzUzXXFNc/GsS0iXkF+OQBOYOuCwdiDsSPrzG3jiYU7U6Rh9B5o6kyG2zyeCfsEUdkRF8Ya5ieGNtJJZgZCG6HOX8lJwNaeQRtQX697PuvTcrjyyKICy2JdkJszA==\""
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     },
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "requestUrl": "https://mastodon.social/ap/users/115705017780116558/following",
Mar 02 09:57:03 grp-service-01 peertube[3632906]:     "requestMethod": "GET"
Mar 02 09:57:03 grp-service-01 peertube[3632906]:   }
Mar 02 09:57:03 grp-service-01 peertube[3632906]: }

Merci, le problème vient de la base de données qui est très lente.

Est-ce que tu peux aller dans la BDD de peertube et me donner le résultat de

EXPLAIN ANALYZE SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" INNER JOIN "actor" "accountActor" ON "account"."id" = "accountActor"."accountId" LEFT JOIN "videoLive" ON "video"."id" = "videoLive"."videoId" LEFT JOIN "videoLiveSchedule" ON "videoLiveSchedule"."liveVideoId" = "videoLive"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '18')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '18')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false) OR ("video"."state" = 4 AND "videoLiveSchedule"."startAt" > NOW())) AND "videoChannel"."id" = 10 AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND "video"."nsfw" IS FALSE   ORDER BY "video"."publishedAt" DESC, "video"."id" ASC LIMIT 25 OFFSET 25

Ouep, voilà

                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17.62..22.46 rows=25 width=16) (actual time=5.112..5.118 rows=0 loops=1)
   ->  Nested Loop Left Join  (cost=12.77..2008.95 rows=10296 width=16) (actual time=0.837..5.112 rows=21 loops=1)
         Filter: ((video.state = 1) OR ((video.state = 2) AND (video."waitTranscoding" IS FALSE)) OR ((video.state = 4) AND ("videoLiveSchedule"."startAt" > now())))
         Rows Removed by Filter: 1
         ->  Nested Loop Anti Join  (cost=12.62..910.02 rows=1694 width=21) (actual time=0.818..5.054 rows=22 loops=1)
               ->  Nested Loop  (cost=12.46..374.87 rows=1821 width=25) (actual time=0.799..4.989 rows=22 loops=1)
                     ->  Nested Loop Anti Join  (cost=12.18..317.03 rows=9 width=29) (actual time=0.766..4.790 rows=22 loops=1)
                           ->  Nested Loop Left Join  (cost=12.02..312.81 rows=9 width=29) (actual time=0.744..4.670 rows=22 loops=1)
                                 Join Filter: (video.id = "videoLive"."videoId")
                                 Rows Removed by Join Filter: 4883
                                 ->  Nested Loop  (cost=12.02..277.07 rows=9 width=25) (actual time=0.444..2.875 rows=22 loops=1)
                                       Join Filter: ("videoChannel"."accountId" = account.id)
                                       Rows Removed by Join Filter: 5588
                                       ->  Nested Loop  (cost=12.02..228.23 rows=9 width=21) (actual time=0.138..0.891 rows=22 loops=1)
                                             ->  Index Scan using video_published_at_id on video  (cost=12.02..226.69 rows=9 width=21) (actual time=0.097..0.806 rows=22 loops=1)
                                                   Filter: ((nsfw IS FALSE) AND (NOT (hashed SubPlan 1)) AND ("channelId" = 10) AND ((privacy = 1) OR (privacy = 4)))
                                                   Rows Removed by Filter: 366
                                                   SubPlan 1
                                                     ->  Seq Scan on "videoBlacklist"  (cost=0.00..11.40 rows=140 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                                             ->  Materialize  (cost=0.00..1.43 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=22)
                                                   ->  Seq Scan on "videoChannel"  (cost=0.00..1.43 rows=1 width=8) (actual time=0.019..0.022 rows=1 loops=1)
                                                         Filter: (id = 10)
                                                         Rows Removed by Filter: 34
                                       ->  Materialize  (cost=0.00..8.56 rows=304 width=4) (actual time=0.001..0.044 rows=255 loops=22)
                                             ->  Seq Scan on account  (cost=0.00..7.04 rows=304 width=4) (actual time=0.010..0.103 rows=255 loops=1)
                                 ->  Materialize  (cost=0.00..6.33 rows=222 width=8) (actual time=0.001..0.041 rows=222 loops=22)
                                       ->  Seq Scan on "videoLive"  (cost=0.00..5.22 rows=222 width=8) (actual time=0.013..0.100 rows=222 loops=1)
                           ->  Index Scan using account_blocklist_target_account_id on "accountBlocklist"  (cost=0.15..0.44 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=22)
                                 Index Cond: ("targetAccountId" = account.id)
                                 Filter: ("accountId" = ANY ('{1,18}'::integer[]))
                     ->  Index Scan using actor_account_id on actor "accountActor"  (cost=0.29..6.43 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=22)
                           Index Cond: ("accountId" = account.id)
               ->  Index Scan using server_blocklist_target_server_id on "serverBlocklist"  (cost=0.15..0.29 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=22)
                     Index Cond: ("targetServerId" = "accountActor"."serverId")
                     Filter: ("accountId" = ANY ('{1,18}'::integer[]))
         ->  Index Scan using video_live_schedule_live_video_id on "videoLiveSchedule"  (cost=0.15..0.49 rows=7 width=12) (actual time=0.001..0.001 rows=0 loops=22)
               Index Cond: ("liveVideoId" = "videoLive".id)
 Planning Time: 15.656 ms
 Execution Time: 5.592 ms
(39 rows)

Bizarre, ta requête semble s’être exécutée en 5ms (VS les 5 000ms en prod). Pourrais-tu essayer

explain analyze SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."accountId" AS "VideoChannel.accountId", "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", "VideoChannel->Actor"."accountId" AS "VideoChannel.Actor.accountId", "VideoChannel->Actor"."videoChannelId" AS "VideoChannel.Actor.videoChannelId", "VideoChannel->Actor->Avatars"."id" AS "VideoChannel.Actor.Avatars.id", "VideoChannel->Actor->Avatars"."width" AS "VideoChannel.Actor.Avatars.width", "VideoChannel->Actor->Avatars"."filename" AS "VideoChannel.Actor.Avatars.filename", "VideoChannel->Actor->Avatars"."type" AS "VideoChannel.Actor.Avatars.type", "VideoChannel->Actor->Avatars"."fileUrl" AS "VideoChannel.Actor.Avatars.fileUrl", "VideoChannel->Actor->Avatars"."onDisk" AS "VideoChannel.Actor.Avatars.onDisk", "VideoChannel->Actor->Avatars"."createdAt" AS "VideoChannel.Actor.Avatars.createdAt", "VideoChannel->Actor->Avatars"."updatedAt" AS "VideoChannel.Actor.Avatars.updatedAt", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."accountId" AS "VideoChannel.Account.Actor.accountId", "VideoChannel->Account->Actor"."videoChannelId" AS "VideoChannel.Account.Actor.videoChannelId", "VideoChannel->Account->Actor->Avatars"."id" AS "VideoChannel.Account.Actor.Avatars.id", "VideoChannel->Account->Actor->Avatars"."width" AS "VideoChannel.Account.Actor.Avatars.width", "VideoChannel->Account->Actor->Avatars"."filename" AS "VideoChannel.Account.Actor.Avatars.filename", "VideoChannel->Account->Actor->Avatars"."type" AS "VideoChannel.Account.Actor.Avatars.type", "VideoChannel->Account->Actor->Avatars"."fileUrl" AS "VideoChannel.Account.Actor.Avatars.fileUrl", "VideoChannel->Account->Actor->Avatars"."onDisk" AS "VideoChannel.Account.Actor.Avatars.onDisk", "VideoChannel->Account->Actor->Avatars"."createdAt" AS "VideoChannel.Account.Actor.Avatars.createdAt", "VideoChannel->Account->Actor->Avatars"."updatedAt" AS "VideoChannel.Account.Actor.Avatars.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime", "VideoLive"."id" AS "VideoLive.id", "VideoLive"."streamKey" AS "VideoLive.streamKey", "VideoLive"."saveReplay" AS "VideoLive.saveReplay", "VideoLive"."permanentLive" AS "VideoLive.permanentLive", "VideoLive"."latencyMode" AS "VideoLive.latencyMode", "VideoLive"."videoId" AS "VideoLive.videoId", "VideoLive"."replaySettingId" AS "VideoLive.replaySettingId", "VideoLive"."createdAt" AS "VideoLive.createdAt", "VideoLive"."updatedAt" AS "VideoLive.updatedAt", "VideoLive->VideoLiveSchedules"."id" AS "VideoLive.VideoLiveSchedules.id", "VideoLive->VideoLiveSchedules"."startAt" AS "VideoLive.VideoLiveSchedules.startAt", "VideoLive->VideoLiveSchedules"."createdAt" AS "VideoLive.VideoLiveSchedules.createdAt", "VideoLive->VideoLiveSchedules"."updatedAt" AS "VideoLive.VideoLiveSchedules.updatedAt" FROM (SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" INNER JOIN "actor" "accountActor" ON "account"."id" = "accountActor"."accountId" LEFT JOIN "videoLive" ON "video"."id" = "videoLive"."videoId" LEFT JOIN "videoLiveSchedule" ON "videoLiveSchedule"."liveVideoId" = "videoLive"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '18')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '18')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false) OR ("video"."state" = 4 AND "videoLiveSchedule"."startAt" > NOW())) AND "videoChannel"."id" = 10 AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND "video"."nsfw" IS FALSE   ORDER BY "video"."publishedAt" DESC, "video"."id" ASC LIMIT 25 OFFSET 25) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."id" = "VideoChannel->Actor"."videoChannelId" 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 INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."id" = "VideoChannel->Account->Actor"."accountId" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatars" ON "VideoChannel->Account->Actor"."id" = "VideoChannel->Account->Actor->Avatars"."actorId" AND "VideoChannel->Account->Actor->Avatars"."type" = 1 LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 3 LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId" LEFT OUTER JOIN "videoLiveSchedule" AS "VideoLive->VideoLiveSchedules" ON "VideoLive->VideoLiveSchedules"."liveVideoId" = "VideoLive"."id"  ORDER BY "video"."publishedAt" DESC, "video"."id" ASC

Et si c’est toujours rapide, me copier plus de lignes des slow logs?

Ah là ça a mis 5 secondes

                                                                                      QUERY PLAN                                                                                       
 Limit  (cost=17.62..22.47 rows=25 width=16) (actual time=5.135..5.142 rows=0 loops=1)
   ->  Nested Loop Left Join  (cost=12.77..2004.32 rows=10265 width=16) (actual time=0.868..5.136 rows=21 loops=1)
         Filter: ((video.state = 1) OR ((video.state = 2) AND (video."waitTranscoding" IS FALSE)) OR ((video.state = 4) AND ("videoLiveSchedule"."startAt" > now())))
         Rows Removed by Filter: 1
         ->  Nested Loop Anti Join  (cost=12.62..908.64 rows=1689 width=21) (actual time=0.852..5.081 rows=22 loops=1)
               ->  Nested Loop  (cost=12.46..374.95 rows=1816 width=25) (actual time=0.834..5.017 rows=22 loops=1)
                     ->  Nested Loop Anti Join  (cost=12.18..317.18 rows=9 width=29) (actual time=0.806..4.822 rows=22 loops=1)
                           ->  Nested Loop Left Join  (cost=12.02..312.96 rows=9 width=29) (actual time=0.784..4.703 rows=22 loops=1)
                                 Join Filter: (video.id = "videoLive"."videoId")
                                 Rows Removed by Join Filter: 4883
                                 ->  Nested Loop  (cost=12.02..277.22 rows=9 width=25) (actual time=0.483..2.936 rows=22 loops=1)
                                       Join Filter: ("videoChannel"."accountId" = account.id)
                                       Rows Removed by Join Filter: 5588
                                       ->  Nested Loop  (cost=12.02..228.23 rows=9 width=21) (actual time=0.139..0.892 rows=22 loops=1)
                                             ->  Index Scan using video_published_at_id on video  (cost=12.02..226.69 rows=9 width=21) (actual time=0.102..0.809 rows=22 loops=1)
                                                   Filter: ((nsfw IS FALSE) AND (NOT (hashed SubPlan 1)) AND ("channelId" = 10) AND ((privacy = 1) OR (privacy = 4)))
                                                   Rows Removed by Filter: 366
                                                   SubPlan 1
                                                     ->  Seq Scan on "videoBlacklist"  (cost=0.00..11.40 rows=140 width=4) (actual time=0.008..0.009 rows=0 loops=1)
                                             ->  Materialize  (cost=0.00..1.43 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=22)
                                                   ->  Seq Scan on "videoChannel"  (cost=0.00..1.43 rows=1 width=8) (actual time=0.019..0.022 rows=1 loops=1)
                                                         Filter: (id = 10)
                                                         Rows Removed by Filter: 34
                                       ->  Materialize  (cost=0.00..8.58 rows=305 width=4) (actual time=0.001..0.047 rows=255 loops=22)
                                             ->  Seq Scan on account  (cost=0.00..7.05 rows=305 width=4) (actual time=0.018..0.137 rows=255 loops=1)
                                 ->  Materialize  (cost=0.00..6.33 rows=222 width=8) (actual time=0.001..0.040 rows=222 loops=22)
                                       ->  Seq Scan on "videoLive"  (cost=0.00..5.22 rows=222 width=8) (actual time=0.014..0.100 rows=222 loops=1)
                           ->  Index Scan using account_blocklist_target_account_id on "accountBlocklist"  (cost=0.15..0.44 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=22)
                                 Index Cond: ("targetAccountId" = account.id)
                                 Filter: ("accountId" = ANY ('{1,18}'::integer[]))
                     ->  Index Scan using actor_account_id on actor "accountActor"  (cost=0.29..6.42 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=22)
                           Index Cond: ("accountId" = account.id)
               ->  Index Scan using server_blocklist_target_server_id on "serverBlocklist"  (cost=0.15..0.29 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=22)
                     Index Cond: ("targetServerId" = "accountActor"."serverId")
                     Filter: ("accountId" = ANY ('{1,18}'::integer[]))
         ->  Index Scan using video_live_schedule_live_video_id on "videoLiveSchedule"  (cost=0.15..0.49 rows=7 width=12) (actual time=0.001..0.001 rows=0 loops=22)
               Index Cond: ("liveVideoId" = "videoLive".id)
 Planning Time: 16.393 ms
 Execution Time: 5.632 ms
                                                                                             Index Cond: ("liveVideoId" = "videoLive".id)
                                                         ->  Index Scan using actor_video_channel_id on actor "VideoChannel->Actor"  (cost=0.29..6.47 rows=1 width=68) (never executed)
                                                               Index Cond: ("videoChannelId" = video."channelId")
                                                   ->  Index Scan using server_pkey on server "VideoChannel->Actor->Server"  (cost=0.14..0.16 rows=1 width=22) (never executed)
                                                         Index Cond: (id = "VideoChannel->Actor"."serverId")
                                             ->  Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Actor->Avatars"  (cost=0.28..0.31 rows=1 width=173) (nev
er executed)
                                                   Index Cond: (("actorId" = "VideoChannel->Actor".id) AND (type = 1))
                                       ->  Materialize  (cost=3.03..109.90 rows=7189 width=319) (never executed)
                                             ->  Nested Loop  (cost=3.03..73.95 rows=7189 width=319) (never executed)
                                                   Join Filter: ("VideoChannel"."accountId" = "VideoChannel->Account".id)
                                                   ->  Merge Join  (cost=2.75..63.65 rows=34 width=306) (never executed)
                                                         Merge Cond: ("VideoChannel->Account->Actor"."accountId" = "VideoChannel"."accountId")
                                                         ->  Index Scan using actor_account_id on actor "VideoChannel->Account->Actor"  (cost=0.29..16553.74 rows=64494 width=68) (never 
executed)
                                                         ->  Sort  (cost=2.20..2.29 rows=34 width=238) (never executed)
                                                               Sort Key: "VideoChannel"."accountId"
                                                               ->  Seq Scan on "videoChannel" "VideoChannel"  (cost=0.00..1.34 rows=34 width=238) (never executed)
                                                   ->  Index Scan using account_pkey on account "VideoChannel->Account"  (cost=0.27..0.29 rows=1 width=13) (never executed)
                                                         Index Cond: (id = "VideoChannel->Account->Actor"."accountId")
                                 ->  Index Scan using server_pkey on server "VideoChannel->Account->Actor->Server"  (cost=0.14..0.16 rows=1 width=22) (never executed)
                                       Index Cond: (id = "VideoChannel->Account->Actor"."serverId")
                           ->  Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Account->Actor->Avatars"  (cost=0.28..0.31 rows=1 width=173) (never execut
ed)
                                 Index Cond: (("actorId" = "VideoChannel->Account->Actor".id) AND (type = 1))
                     ->  Index Scan using user_video_history_user_id_video_id on "userVideoHistory"  (cost=0.14..0.22 rows=1 width=12) (never executed)
                           Index Cond: (("userId" = 3) AND ("videoId" = video.id))
               ->  Index Scan using video_live_video_id on "videoLive" "VideoLive"  (cost=0.14..0.21 rows=1 width=71) (never executed)
                     Index Cond: ("videoId" = video.id)
         ->  Index Scan using thumbnail_video_id on thumbnail "Thumbnails"  (cost=0.28..0.65 rows=2 width=53) (never executed)
               Index Cond: ("videoId" = video.id)
   ->  Index Scan using video_live_schedule_live_video_id on "videoLiveSchedule" "VideoLive->VideoLiveSchedules"  (cost=0.15..0.49 rows=7 width=32) (never executed)
         Index Cond: ("liveVideoId" = "VideoLive".id)
 Planning Time: 34.854 ms
 JIT:
   Functions: 137
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 45.150 ms, Inlining 172.976 ms, Optimization 3456.443 ms, Emission 1846.034 ms, Total 5520.602 ms
 Execution Time: 5575.257 ms
(88 rows)

Merci, mais il semble y avoir un soucis dans le copier/coller ("j’ai un planning time qui se ballade au milieu du texte)

Zut, j’ai recommencé mais ya toujours le planning time

postgres=# \c peertube_prod
psql (14.17 (Ubuntu 14.17-0ubuntu0.22.04.1), server 12.22 (Ubuntu 12.22-0ubuntu0.20.04.2))
You are now connected to database "peertube_prod" as user "postgres".
peertube_prod=# explain analyze SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."accountId" AS "VideoChannel.accountId", "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", "VideoChannel->Actor"."accountId" AS "VideoChannel.Actor.accountId", "VideoChannel->Actor"."videoChannelId" AS "VideoChannel.Actor.videoChannelId", "VideoChannel->Actor->Avatars"."id" AS "VideoChannel.Actor.Avatars.id", "VideoChannel->Actor->Avatars"."width" AS "VideoChannel.Actor.Avatars.width", "VideoChannel->Actor->Avatars"."filename" AS "VideoChannel.Actor.Avatars.filename", "VideoChannel->Actor->Avatars"."type" AS "VideoChannel.Actor.Avatars.type", "VideoChannel->Actor->Avatars"."fileUrl" AS "VideoChannel.Actor.Avatars.fileUrl", "VideoChannel->Actor->Avatars"."onDisk" AS "VideoChannel.Actor.Avatars.onDisk", "VideoChannel->Actor->Avatars"."createdAt" AS "VideoChannel.Actor.Avatars.createdAt", "VideoChannel->Actor->Avatars"."updatedAt" AS "VideoChannel.Actor.Avatars.updatedAt", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."accountId" AS "VideoChannel.Account.Actor.accountId", "VideoChannel->Account->Actor"."videoChannelId" AS "VideoChannel.Account.Actor.videoChannelId", "VideoChannel->Account->Actor->Avatars"."id" AS "VideoChannel.Account.Actor.Avatars.id", "VideoChannel->Account->Actor->Avatars"."width" AS "VideoChannel.Account.Actor.Avatars.width", "VideoChannel->Account->Actor->Avatars"."filename" AS "VideoChannel.Account.Actor.Avatars.filename", "VideoChannel->Account->Actor->Avatars"."type" AS "VideoChannel.Account.Actor.Avatars.type", "VideoChannel->Account->Actor->Avatars"."fileUrl" AS "VideoChannel.Account.Actor.Avatars.fileUrl", "VideoChannel->Account->Actor->Avatars"."onDisk" AS "VideoChannel.Account.Actor.Avatars.onDisk", "VideoChannel->Account->Actor->Avatars"."createdAt" AS "VideoChannel.Account.Actor.Avatars.createdAt", "VideoChannel->Account->Actor->Avatars"."updatedAt" AS "VideoChannel.Account.Actor.Avatars.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime", "VideoLive"."id" AS "VideoLive.id", "VideoLive"."streamKey" AS "VideoLive.streamKey", "VideoLive"."saveReplay" AS "VideoLive.saveReplay", "VideoLive"."permanentLive" AS "VideoLive.permanentLive", "VideoLive"."latencyMode" AS "VideoLive.latencyMode", "VideoLive"."videoId" AS "VideoLive.videoId", "VideoLive"."replaySettingId" AS "VideoLive.replaySettingId", "VideoLive"."createdAt" AS "VideoLive.createdAt", "VideoLive"."updatedAt" AS "VideoLive.updatedAt", "VideoLive->VideoLiveSchedules"."id" AS "VideoLive.VideoLiveSchedules.id", "VideoLive->VideoLiveSchedules"."startAt" AS "VideoLive.VideoLiveSchedules.startAt", "VideoLive->VideoLiveSchedules"."createdAt" AS "VideoLive.VideoLiveSchedules.createdAt", "VideoLive->VideoLiveSchedules"."updatedAt" AS "VideoLive.VideoLiveSchedules.updatedAt" FROM (SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" INNER JOIN "actor" "accountActor" ON "account"."id" = "accountActor"."accountId" LEFT JOIN "videoLive" ON "video"."id" = "videoLive"."videoId" LEFT JOIN "videoLiveSchedule" ON "videoLiveSchedule"."liveVideoId" = "videoLive"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '18')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '18')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false) OR ("video"."state" = 4 AND "videoLiveSchedule"."startAt" > NOW())) AND "videoChannel"."id" = 10 AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND "video"."nsfw" IS FALSE   ORDER BY "video"."publishedAt" DESC, "video"."id" ASC LIMIT 25 OFFSET 25) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."id" = "VideoChannel->Actor"."videoChannelId" 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 INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."id" = "VideoChannel->Account->Actor"."accountId" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatars" ON "VideoChannel->Account->Actor"."id" = "VideoChannel->Account->Actor->Avatars"."actorId" AND "VideoChannel->Account->Actor->Avatars"."type" = 1 LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 3 LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId" LEFT OUTER JOIN "videoLiveSchedule" AS "VideoLive->VideoLiveSchedules" ON "VideoLive->VideoLiveSchedules"."liveVideoId" = "VideoLive"."id"  ORDER BY "video"."publishedAt" DESC, "video"."id" ASC;
                                                                                                                                 QUERY PLAN                                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=22.78..27750190.31 rows=129986037 width=2180) (actual time=5506.873..5506.891 rows=0 loops=1)
   ->  Nested Loop Left Join  (cost=22.62..15842131.21 rows=21218309 width=2152) (actual time=5506.872..5506.889 rows=0 loops=1)
         ->  Nested Loop Left Join  (cost=22.35..9121535.48 rows=10027654 width=2103) (actual time=5506.871..5506.887 rows=0 loops=1)
               ->  Nested Loop Left Join  (cost=22.20..6976918.43 rows=10027654 width=2032) (actual time=5506.870..5506.886 rows=0 loops=1)
                     ->  Nested Loop Left Join  (cost=22.06..4807204.27 rows=10027654 width=2024) (actual time=5506.870..5506.884 rows=0 loops=1)
                           ->  Nested Loop Left Join  (cost=21.78..1635194.88 rows=10027654 width=1855) (actual time=5506.869..5506.883 rows=0 loops=1)
                                 ->  Nested Loop  (cost=21.63..3340.93 rows=10027654 width=1833) (actual time=5506.868..5506.881 rows=0 loops=1)
                                       Join Filter: ("VideoChannel".id = video."channelId")
                                       ->  Nested Loop Left Join  (cost=18.61..553.13 rows=25 width=1514) (actual time=5506.867..5506.877 rows=0 loops=1)
                                             ->  Nested Loop Left Join  (cost=18.33..545.22 rows=25 width=1345) (actual time=5506.866..5506.876 rows=0 loops=1)
                                                   ->  Nested Loop  (cost=18.18..541.15 rows=25 width=1323) (actual time=5506.865..5506.874 rows=0 loops=1)
                                                         ->  Nested Loop  (cost=17.89..379.34 rows=25 width=1255) (actual time=5506.865..5506.873 rows=0 loops=1)
                                                               Join Filter: (tmp.id = video.id)
                                                               ->  Index Scan using video_published_at_id on video  (cost=0.27..211.06 rows=388 width=1255) (actual time=5370.083..5370.590 rows=388 loops=1)
                                                               ->  Materialize  (cost=17.62..22.85 rows=25 width=4) (actual time=0.351..0.351 rows=0 loops=388)
                                                                     ->  Subquery Scan on tmp  (cost=17.62..22.72 rows=25 width=4) (actual time=136.074..136.081 rows=0 loops=1)
                                                                           ->  Limit  (cost=17.62..22.47 rows=25 width=16) (actual time=136.073..136.079 rows=0 loops=1)
                                                                                 ->  Nested Loop Left Join  (cost=12.77..2004.32 rows=10265 width=16) (actual time=132.446..136.065 rows=21 loops=1)
                                                                                       Filter: ((video_1.state = 1) OR ((video_1.state = 2) AND (video_1."waitTranscoding" IS FALSE)) OR ((video_1.state = 4) AND ("videoLiveSchedule"."startAt" > now())))
                                                                                       Rows Removed by Filter: 1
                                                                                       ->  Nested Loop Anti Join  (cost=12.62..908.64 rows=1689 width=21) (actual time=132.434..136.014 rows=22 loops=1)
                                                                                             ->  Nested Loop  (cost=12.46..374.95 rows=1816 width=25) (actual time=132.423..135.960 rows=22 loops=1)
                                                                                                   ->  Nested Loop Anti Join  (cost=12.18..317.18 rows=9 width=29) (actual time=132.394..135.772 rows=22 loops=1)
                                                                                                         ->  Nested Loop Left Join  (cost=12.02..312.96 rows=9 width=29) (actual time=132.370..135.650 rows=22 loops=1)
                                                                                                               Join Filter: (video_1.id = "videoLive"."videoId")
                                                                                                               Rows Removed by Join Filter: 4883
                                                                                                               ->  Nested Loop  (cost=12.02..277.22 rows=9 width=25) (actual time=132.091..134.100 rows=22 loops=1)
                                                                                                                     Join Filter: ("videoChannel"."accountId" = account.id)
                                                                                                                     Rows Removed by Join Filter: 5588
                                                                                                                     ->  Nested Loop  (cost=12.02..228.23 rows=9 width=21) (actual time=131.767..132.322 rows=22 loops=1)
                                                                                                                           ->  Index Scan using video_published_at_id on video video_1  (cost=12.02..226.69 rows=9 width=21) (actual time=131.625..132.140 rows=22 loops=1)
                                                                                                                                 Filter: ((nsfw IS FALSE) AND (NOT (hashed SubPlan 1)) AND ("channelId" = 10) AND ((privacy = 1) OR (privacy = 4)))
                                                                                                                                 Rows Removed by Filter: 366
                                                                                                                                 SubPlan 1
                                                                                                                                   ->  Seq Scan on "videoBlacklist"  (cost=0.00..11.40 rows=140 width=4) (actual time=0.010..0.011 rows=0 loops=1)
                                                                                                                           ->  Materialize  (cost=0.00..1.43 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=22)
                                                                                                                                 ->  Seq Scan on "videoChannel"  (cost=0.00..1.43 rows=1 width=8) (actual time=0.040..0.043 rows=1 loops=1)
                                                                                                                                       Filter: (id = 10)
                                                                                                                                       Rows Removed by Filter: 34
                                                                                                                     ->  Materialize  (cost=0.00..8.58 rows=305 width=4) (actual time=0.003..0.045 rows=255 loops=22)
                                                                                                                           ->  Seq Scan on account  (cost=0.00..7.05 rows=305 width=4) (actual time=0.037..0.120 rows=255 loops=1)
                                                                                                               ->  Materialize  (cost=0.00..6.33 rows=222 width=8) (actual time=0.001..0.039 rows=222 loops=22)
                                                                                                                     ->  Seq Scan on "videoLive"  (cost=0.00..5.22 rows=222 width=8) (actual time=0.017..0.082 rows=222 loops=1)
                                                                                                         ->  Index Scan using account_blocklist_target_account_id on "accountBlocklist"  (cost=0.15..0.44 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=22)
                                                                                                               Index Cond: ("targetAccountId" = account.id)
                                                                                                               Filter: ("accountId" = ANY ('{1,18}'::integer[]))
                                                                                                   ->  Index Scan using actor_account_id on actor "accountActor"  (cost=0.29..6.42 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=22)
                                                                                                         Index Cond: ("accountId" = account.id)
                                                                                             ->  Index Scan using server_blocklist_target_server_id on "serverBlocklist"  (cost=0.15..0.29 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=22)
                                                                                                   Index Cond: ("targetServerId" = "accountActor"."serverId")
                                                                                                   Filter: ("accountId" = ANY ('{1,18}'::integer[]))
                                                                                       ->  Index Scan using video_live_schedule_live_video_id on "videoLiveSchedule"  (cost=0.15..0.49 rows=7 width=12) (actual time=0.001..0.001 rows=0 loops=22)
                                                                                             Index Cond: ("liveVideoId" = "videoLive".id)
                                                         ->  Index Scan using actor_video_channel_id on actor "VideoChannel->Actor"  (cost=0.29..6.47 rows=1 width=68) (never executed)
                                                               Index Cond: ("videoChannelId" = video."channelId")
                                                   ->  Index Scan using server_pkey on server "VideoChannel->Actor->Server"  (cost=0.14..0.16 rows=1 width=22) (never executed)
                                                         Index Cond: (id = "VideoChannel->Actor"."serverId")
                                             ->  Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Actor->Avatars"  (cost=0.28..0.31 rows=1 width=173) (never executed)
                                                   Index Cond: (("actorId" = "VideoChannel->Actor".id) AND (type = 1))
                                       ->  Materialize  (cost=3.03..109.90 rows=7189 width=319) (never executed)
                                             ->  Nested Loop  (cost=3.03..73.95 rows=7189 width=319) (never executed)
                                                   Join Filter: ("VideoChannel"."accountId" = "VideoChannel->Account".id)
                                                   ->  Merge Join  (cost=2.75..63.65 rows=34 width=306) (never executed)
                                                         Merge Cond: ("VideoChannel->Account->Actor"."accountId" = "VideoChannel"."accountId")
                                                         ->  Index Scan using actor_account_id on actor "VideoChannel->Account->Actor"  (cost=0.29..16553.74 rows=64494 width=68) (never executed)
                                                         ->  Sort  (cost=2.20..2.29 rows=34 width=238) (never executed)
                                                               Sort Key: "VideoChannel"."accountId"
                                                               ->  Seq Scan on "videoChannel" "VideoChannel"  (cost=0.00..1.34 rows=34 width=238) (never executed)
                                                   ->  Index Scan using account_pkey on account "VideoChannel->Account"  (cost=0.27..0.29 rows=1 width=13) (never executed)
                                                         Index Cond: (id = "VideoChannel->Account->Actor"."accountId")
                                 ->  Index Scan using server_pkey on server "VideoChannel->Account->Actor->Server"  (cost=0.14..0.16 rows=1 width=22) (never executed)
                                       Index Cond: (id = "VideoChannel->Account->Actor"."serverId")
                           ->  Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Account->Actor->Avatars"  (cost=0.28..0.31 rows=1 width=173) (never executed)
                                 Index Cond: (("actorId" = "VideoChannel->Account->Actor".id) AND (type = 1))
                     ->  Index Scan using user_video_history_user_id_video_id on "userVideoHistory"  (cost=0.14..0.22 rows=1 width=12) (never executed)
                           Index Cond: (("userId" = 3) AND ("videoId" = video.id))
               ->  Index Scan using video_live_video_id on "videoLive" "VideoLive"  (cost=0.14..0.21 rows=1 width=71) (never executed)
                     Index Cond: ("videoId" = video.id)
         ->  Index Scan using thumbnail_video_id on thumbnail "Thumbnails"  (cost=0.28..0.65 rows=2 width=53) (never executed)
               Index Cond: ("videoId" = video.id)
   ->  Index Scan using video_live_schedule_live_video_id on "videoLiveSchedule" "VideoLive->VideoLiveSchedules"  (cost=0.15..0.49 rows=7 width=32) (never executed)
         Index Cond: ("liveVideoId" = "VideoLive".id)
 Planning Time: 43.199 ms
 JIT:
   Functions: 137
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 45.601 ms, Inlining 173.331 ms, Optimization 3471.686 ms, Emission 1846.946 ms, Total 5537.565 ms
 Execution Time: 5610.424 ms
(88 rows)

peertube_prod=# 







PostgreSQL semble complètement perdu car il estime retourner 129986037 lignes. Tente un analyze; dans la bdd pour regénérer les stats et refait l’explain pour vérifier si ça a corrigé le soucis ?

peertube_prod=# analyze;
ANALYZE
peertube_prod=# explain analyze SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."accountId" AS "VideoChannel.accountId", "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", "VideoChannel->Actor"."accountId" AS "VideoChannel.Actor.accountId", "VideoChannel->Actor"."videoChannelId" AS "VideoChannel.Actor.videoChannelId", "VideoChannel->Actor->Avatars"."id" AS "VideoChannel.Actor.Avatars.id", "VideoChannel->Actor->Avatars"."width" AS "VideoChannel.Actor.Avatars.width", "VideoChannel->Actor->Avatars"."filename" AS "VideoChannel.Actor.Avatars.filename", "VideoChannel->Actor->Avatars"."type" AS "VideoChannel.Actor.Avatars.type", "VideoChannel->Actor->Avatars"."fileUrl" AS "VideoChannel.Actor.Avatars.fileUrl", "VideoChannel->Actor->Avatars"."onDisk" AS "VideoChannel.Actor.Avatars.onDisk", "VideoChannel->Actor->Avatars"."createdAt" AS "VideoChannel.Actor.Avatars.createdAt", "VideoChannel->Actor->Avatars"."updatedAt" AS "VideoChannel.Actor.Avatars.updatedAt", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."accountId" AS "VideoChannel.Account.Actor.accountId", "VideoChannel->Account->Actor"."videoChannelId" AS "VideoChannel.Account.Actor.videoChannelId", "VideoChannel->Account->Actor->Avatars"."id" AS "VideoChannel.Account.Actor.Avatars.id", "VideoChannel->Account->Actor->Avatars"."width" AS "VideoChannel.Account.Actor.Avatars.width", "VideoChannel->Account->Actor->Avatars"."filename" AS "VideoChannel.Account.Actor.Avatars.filename", "VideoChannel->Account->Actor->Avatars"."type" AS "VideoChannel.Account.Actor.Avatars.type", "VideoChannel->Account->Actor->Avatars"."fileUrl" AS "VideoChannel.Account.Actor.Avatars.fileUrl", "VideoChannel->Account->Actor->Avatars"."onDisk" AS "VideoChannel.Account.Actor.Avatars.onDisk", "VideoChannel->Account->Actor->Avatars"."createdAt" AS "VideoChannel.Account.Actor.Avatars.createdAt", "VideoChannel->Account->Actor->Avatars"."updatedAt" AS "VideoChannel.Account.Actor.Avatars.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime", "VideoLive"."id" AS "VideoLive.id", "VideoLive"."streamKey" AS "VideoLive.streamKey", "VideoLive"."saveReplay" AS "VideoLive.saveReplay", "VideoLive"."permanentLive" AS "VideoLive.permanentLive", "VideoLive"."latencyMode" AS "VideoLive.latencyMode", "VideoLive"."videoId" AS "VideoLive.videoId", "VideoLive"."replaySettingId" AS "VideoLive.replaySettingId", "VideoLive"."createdAt" AS "VideoLive.createdAt", "VideoLive"."updatedAt" AS "VideoLive.updatedAt", "VideoLive->VideoLiveSchedules"."id" AS "VideoLive.VideoLiveSchedules.id", "VideoLive->VideoLiveSchedules"."startAt" AS "VideoLive.VideoLiveSchedules.startAt", "VideoLive->VideoLiveSchedules"."createdAt" AS "VideoLive.VideoLiveSchedules.createdAt", "VideoLive->VideoLiveSchedules"."updatedAt" AS "VideoLive.VideoLiveSchedules.updatedAt" FROM (SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" INNER JOIN "actor" "accountActor" ON "account"."id" = "accountActor"."accountId" LEFT JOIN "videoLive" ON "video"."id" = "videoLive"."videoId" LEFT JOIN "videoLiveSchedule" ON "videoLiveSchedule"."liveVideoId" = "videoLive"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '18')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '18')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false) OR ("video"."state" = 4 AND "videoLiveSchedule"."startAt" > NOW())) AND "videoChannel"."id" = 10 AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND "video"."nsfw" IS FALSE   ORDER BY "video"."publishedAt" DESC, "video"."id" ASC LIMIT 25 OFFSET 25) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."id" = "VideoChannel->Actor"."videoChannelId" 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 INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."id" = "VideoChannel->Account->Actor"."accountId" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatars" ON "VideoChannel->Account->Actor"."id" = "VideoChannel->Account->Actor->Avatars"."actorId" AND "VideoChannel->Account->Actor->Avatars"."type" = 1 LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 3 LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId" LEFT OUTER JOIN "videoLiveSchedule" AS "VideoLive->VideoLiveSchedules" ON "VideoLive->VideoLiveSchedules"."liveVideoId" = "VideoLive"."id"  ORDER BY "video"."publishedAt" DESC, "video"."id" ASC;
peertube_prod=# explain analyze SELECT "video".*, "VideoChannel"."id" AS "VideoChannel.id", "VideoChannel"."name" AS "VideoChannel.name", "VideoChannel"."description" AS "VideoChannel.description", "VideoChannel"."accountId" AS "VideoChannel.accountId", "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", "VideoChannel->Actor"."accountId" AS "VideoChannel.Actor.accountId", "VideoChannel->Actor"."videoChannelId" AS "VideoChannel.Actor.videoChannelId", "VideoChannel->Actor->Avatars"."id" AS "VideoChannel.Actor.Avatars.id", "VideoChannel->Actor->Avatars"."width" AS "VideoChannel.Actor.Avatars.width", "VideoChannel->Actor->Avatars"."filename" AS "VideoChannel.Actor.Avatars.filename", "VideoChannel->Actor->Avatars"."type" AS "VideoChannel.Actor.Avatars.type", "VideoChannel->Actor->Avatars"."fileUrl" AS "VideoChannel.Actor.Avatars.fileUrl", "VideoChannel->Actor->Avatars"."onDisk" AS "VideoChannel.Actor.Avatars.onDisk", "VideoChannel->Actor->Avatars"."createdAt" AS "VideoChannel.Actor.Avatars.createdAt", "VideoChannel->Actor->Avatars"."updatedAt" AS "VideoChannel.Actor.Avatars.updatedAt", "VideoChannel->Actor->Server"."id" AS "VideoChannel.Actor.Server.id", "VideoChannel->Actor->Server"."host" AS "VideoChannel.Actor.Server.host", "VideoChannel->Account"."id" AS "VideoChannel.Account.id", "VideoChannel->Account"."name" AS "VideoChannel.Account.name", "VideoChannel->Account->Actor"."id" AS "VideoChannel.Account.Actor.id", "VideoChannel->Account->Actor"."preferredUsername" AS "VideoChannel.Account.Actor.preferredUsername", "VideoChannel->Account->Actor"."url" AS "VideoChannel.Account.Actor.url", "VideoChannel->Account->Actor"."serverId" AS "VideoChannel.Account.Actor.serverId", "VideoChannel->Account->Actor"."accountId" AS "VideoChannel.Account.Actor.accountId", "VideoChannel->Account->Actor"."videoChannelId" AS "VideoChannel.Account.Actor.videoChannelId", "VideoChannel->Account->Actor->Avatars"."id" AS "VideoChannel.Account.Actor.Avatars.id", "VideoChannel->Account->Actor->Avatars"."width" AS "VideoChannel.Account.Actor.Avatars.width", "VideoChannel->Account->Actor->Avatars"."filename" AS "VideoChannel.Account.Actor.Avatars.filename", "VideoChannel->Account->Actor->Avatars"."type" AS "VideoChannel.Account.Actor.Avatars.type", "VideoChannel->Account->Actor->Avatars"."fileUrl" AS "VideoChannel.Account.Actor.Avatars.fileUrl", "VideoChannel->Account->Actor->Avatars"."onDisk" AS "VideoChannel.Account.Actor.Avatars.onDisk", "VideoChannel->Account->Actor->Avatars"."createdAt" AS "VideoChannel.Account.Actor.Avatars.createdAt", "VideoChannel->Account->Actor->Avatars"."updatedAt" AS "VideoChannel.Account.Actor.Avatars.updatedAt", "VideoChannel->Account->Actor->Server"."id" AS "VideoChannel.Account.Actor.Server.id", "VideoChannel->Account->Actor->Server"."host" AS "VideoChannel.Account.Actor.Server.host", "Thumbnails"."id" AS "Thumbnails.id", "Thumbnails"."type" AS "Thumbnails.type", "Thumbnails"."filename" AS "Thumbnails.filename", "userVideoHistory"."id" AS "userVideoHistory.id", "userVideoHistory"."currentTime" AS "userVideoHistory.currentTime", "VideoLive"."id" AS "VideoLive.id", "VideoLive"."streamKey" AS "VideoLive.streamKey", "VideoLive"."saveReplay" AS "VideoLive.saveReplay", "VideoLive"."permanentLive" AS "VideoLive.permanentLive", "VideoLive"."latencyMode" AS "VideoLive.latencyMode", "VideoLive"."videoId" AS "VideoLive.videoId", "VideoLive"."replaySettingId" AS "VideoLive.replaySettingId", "VideoLive"."createdAt" AS "VideoLive.createdAt", "VideoLive"."updatedAt" AS "VideoLive.updatedAt", "VideoLive->VideoLiveSchedules"."id" AS "VideoLive.VideoLiveSchedules.id", "VideoLive->VideoLiveSchedules"."startAt" AS "VideoLive.VideoLiveSchedules.startAt", "VideoLive->VideoLiveSchedules"."createdAt" AS "VideoLive.VideoLiveSchedules.createdAt", "VideoLive->VideoLiveSchedules"."updatedAt" AS "VideoLive.VideoLiveSchedules.updatedAt" FROM (SELECT "video"."id", 0 as similarity FROM "video" INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" INNER JOIN "actor" "accountActor" ON "account"."id" = "accountActor"."accountId" LEFT JOIN "videoLive" ON "video"."id" = "videoLive"."videoId" LEFT JOIN "videoLiveSchedule" ON "videoLiveSchedule"."liveVideoId" = "videoLive"."id" WHERE "video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist") AND NOT EXISTS (  SELECT 1 FROM "accountBlocklist"   WHERE "accountBlocklist"."accountId" IN ('1', '18')   AND "accountBlocklist"."targetAccountId" = "account"."id" )AND NOT EXISTS (  SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN ('1', '18')   AND "serverBlocklist"."targetServerId" = "accountActor"."serverId") AND ("video"."state" = 1 OR ("video"."state" = 2 AND "video"."waitTranscoding" IS false) OR ("video"."state" = 4 AND "videoLiveSchedule"."startAt" > NOW())) AND "videoChannel"."id" = 10 AND ("video"."privacy" = 1 OR "video"."privacy" = 4) AND "video"."nsfw" IS FALSE   ORDER BY "video"."publishedAt" DESC, "video"."id" ASC LIMIT 25 OFFSET 25) AS "tmp" INNER JOIN "video" ON "tmp"."id" = "video"."id" INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id" INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."id" = "VideoChannel->Actor"."videoChannelId" 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 INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id" INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."id" = "VideoChannel->Account->Actor"."accountId" LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id" LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatars" ON "VideoChannel->Account->Actor"."id" = "VideoChannel->Account->Actor->Avatars"."actorId" AND "VideoChannel->Account->Actor->Avatars"."type" = 1 LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId" LEFT OUTER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = 3 LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId" LEFT OUTER JOIN "videoLiveSchedule" AS "VideoLive->VideoLiveSchedules" ON "VideoLive->VideoLiveSchedules"."liveVideoId" = "VideoLive"."id"  ORDER BY "video"."publishedAt" DESC, "video"."id" ASC;
                                                                                                                                    QUERY PLAN                                                                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=31.45..15561602.72 rows=20714321 width=2168) (actual time=5601.371..5601.389 rows=0 loops=1)
   ->  Nested Loop Left Join  (cost=31.18..9032448.70 rows=9742008 width=2119) (actual time=5601.370..5601.387 rows=0 loops=1)
         Join Filter: ("VideoLive->VideoLiveSchedules"."liveVideoId" = "VideoLive".id)
         ->  Nested Loop Left Join  (cost=31.18..8886317.58 rows=9742008 width=2091) (actual time=5601.368..5601.385 rows=0 loops=1)
               ->  Nested Loop Left Join  (cost=31.03..6802791.73 rows=9742008 width=2020) (actual time=5601.368..5601.383 rows=0 loops=1)
                     ->  Nested Loop Left Join  (cost=30.89..4694883.68 rows=9742008 width=2012) (actual time=5601.367..5601.382 rows=0 loops=1)
                           ->  Nested Loop Left Join  (cost=30.61..1613234.47 rows=9742008 width=1843) (actual time=5601.366..5601.380 rows=0 loops=1)
                                 ->  Nested Loop  (cost=30.46..3441.22 rows=9742008 width=1821) (actual time=5601.365..5601.379 rows=0 loops=1)
                                       Join Filter: ("VideoChannel".id = video."channelId")
                                       ->  Nested Loop Left Join  (cost=27.65..567.83 rows=25 width=1514) (actual time=5601.365..5601.376 rows=0 loops=1)
                                             ->  Nested Loop Left Join  (cost=27.37..559.92 rows=25 width=1345) (actual time=5601.364..5601.374 rows=0 loops=1)
                                                   ->  Nested Loop  (cost=27.22..555.79 rows=25 width=1323) (actual time=5601.363..5601.373 rows=0 loops=1)
                                                         ->  Nested Loop  (cost=26.93..393.97 rows=25 width=1255) (actual time=5601.362..5601.371 rows=0 loops=1)
                                                               Join Filter: (tmp.id = video.id)
                                                               ->  Index Scan using video_published_at_id on video  (cost=0.27..212.14 rows=388 width=1255) (actual time=5467.361..5467.844 rows=388 loops=1)
                                                               ->  Materialize  (cost=26.66..36.40 rows=25 width=4) (actual time=0.344..0.344 rows=0 loops=388)
                                                                     ->  Subquery Scan on tmp  (cost=26.66..36.27 rows=25 width=4) (actual time=133.316..133.324 rows=0 loops=1)
                                                                           ->  Limit  (cost=26.66..36.02 rows=25 width=16) (actual time=133.315..133.322 rows=0 loops=1)
                                                                                 ->  Nested Loop Anti Join  (cost=17.30..644.58 rows=1675 width=16) (actual time=132.435..133.308 rows=21 loops=1)
                                                                                       Join Filter: ("accountBlocklist"."targetAccountId" = account.id)
                                                                                       ->  Nested Loop  (cost=12.88..260.81 rows=1756 width=16) (actual time=132.417..133.267 rows=21 loops=1)
                                                                                             Join Filter: ("videoChannel"."accountId" = account.id)
                                                                                             ->  Nested Loop Left Join  (cost=12.61..258.39 rows=8 width=20) (actual time=132.392..133.118 rows=21 loops=1)
                                                                                                   Join Filter: ("videoLiveSchedule"."liveVideoId" = "videoLive".id)
                                                                                                   Filter: ((video_1.state = 1) OR ((video_1.state = 2) AND (video_1."waitTranscoding" IS FALSE)) OR ((video_1.state = 4) AND ("videoLiveSchedule"."startAt" > now())))
                                                                                                   Rows Removed by Filter: 1
                                                                                                   ->  Nested Loop Left Join  (cost=12.61..257.16 rows=8 width=29) (actual time=132.373..133.075 rows=22 loops=1)
                                                                                                         ->  Nested Loop  (cost=12.47..238.06 rows=8 width=25) (actual time=132.355..132.937 rows=22 loops=1)
                                                                                                               ->  Index Scan using video_published_at_id on video video_1  (cost=12.02..227.77 rows=9 width=21) (actual time=132.252..132.798 rows=22 loops=1)
                                                                                                                     Filter: ((nsfw IS FALSE) AND (NOT (hashed SubPlan 1)) AND ("channelId" = 10) AND ((privacy = 1) OR (privacy = 4)))
                                                                                                                     Rows Removed by Filter: 366
                                                                                                                     SubPlan 1
                                                                                                                       ->  Seq Scan on "videoBlacklist"  (cost=0.00..11.40 rows=140 width=4) (actual time=0.012..0.012 rows=0 loops=1)
                                                                                                               ->  Materialize  (cost=0.44..10.18 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=22)
                                                                                                                     ->  Nested Loop Anti Join  (cost=0.44..10.18 rows=1 width=12) (actual time=0.090..0.094 rows=1 loops=1)
                                                                                                                           ->  Nested Loop  (cost=0.29..9.75 rows=1 width=16) (actual time=0.078..0.082 rows=1 loops=1)
                                                                                                                                 ->  Seq Scan on "videoChannel"  (cost=0.00..1.44 rows=1 width=8) (actual time=0.039..0.042 rows=1 loops=1)
                                                                                                                                       Filter: (id = 10)
                                                                                                                                       Rows Removed by Filter: 34
                                                                                                                                 ->  Index Scan using actor_account_id on actor "accountActor"  (cost=0.29..8.31 rows=1 width=8) (actual time=0.030..0.030 rows=1 loops=1)
                                                                                                                                       Index Cond: ("accountId" = "videoChannel"."accountId")
                                                                                                                           ->  Index Scan using server_blocklist_target_server_id on "serverBlocklist"  (cost=0.15..0.29 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=1)
                                                                                                                                 Index Cond: ("targetServerId" = "accountActor"."serverId")
                                                                                                                                 Filter: ("accountId" = ANY ('{1,18}'::integer[]))
                                                                                                         ->  Index Scan using video_live_video_id on "videoLive"  (cost=0.14..2.38 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=22)
                                                                                                               Index Cond: ("videoId" = video_1.id)
                                                                                                   ->  Materialize  (cost=0.00..1.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=22)
                                                                                                         ->  Seq Scan on "videoLiveSchedule"  (cost=0.00..1.00 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=1)
                                                                                             ->  Index Only Scan using account_pkey on account  (cost=0.27..0.29 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=21)
                                                                                                   Index Cond: (id = "accountActor"."accountId")
                                                                                                   Heap Fetches: 0
                                                                                       ->  Materialize  (cost=4.41..15.04 rows=14 width=4) (actual time=0.001..0.001 rows=0 loops=21)
                                                                                             ->  Bitmap Heap Scan on "accountBlocklist"  (cost=4.41..14.97 rows=14 width=4) (actual time=0.010..0.011 rows=0 loops=1)
                                                                                                   Recheck Cond: ("accountId" = ANY ('{1,18}'::integer[]))
                                                                                                   ->  Bitmap Index Scan on account_blocklist_account_id_target_account_id  (cost=0.00..4.41 rows=14 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                                                                                                         Index Cond: ("accountId" = ANY ('{1,18}'::integer[]))
                                                         ->  Index Scan using actor_video_channel_id on actor "VideoChannel->Actor"  (cost=0.29..6.47 rows=1 width=68) (never executed)
                                                               Index Cond: ("videoChannelId" = video."channelId")
                                                   ->  Index Scan using server_pkey on server "VideoChannel->Actor->Server"  (cost=0.15..0.17 rows=1 width=22) (never executed)
                                                         Index Cond: (id = "VideoChannel->Actor"."serverId")
                                             ->  Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Actor->Avatars"  (cost=0.28..0.31 rows=1 width=173) (never executed)
                                                   Index Cond: (("actorId" = "VideoChannel->Actor".id) AND (type = 1))
                                       ->  Materialize  (cost=2.81..116.52 rows=7401 width=307) (never executed)
                                             ->  Nested Loop  (cost=2.81..79.52 rows=7401 width=307) (never executed)
                                                   Join Filter: ("VideoChannel"."accountId" = "VideoChannel->Account".id)
                                                   ->  Merge Join  (cost=2.54..68.91 rows=35 width=294) (never executed)
                                                         Merge Cond: ("VideoChannel->Account->Actor"."accountId" = "VideoChannel"."accountId")
                                                         ->  Index Scan using actor_account_id on actor "VideoChannel->Account->Actor"  (cost=0.29..18631.10 rows=64497 width=68) (never executed)
                                                         ->  Sort  (cost=2.25..2.34 rows=35 width=226) (never executed)
                                                               Sort Key: "VideoChannel"."accountId"
                                                               ->  Seq Scan on "videoChannel" "VideoChannel"  (cost=0.00..1.35 rows=35 width=226) (never executed)
                                                   ->  Index Scan using account_pkey on account "VideoChannel->Account"  (cost=0.27..0.29 rows=1 width=13) (never executed)
                                                         Index Cond: (id = "VideoChannel->Account->Actor"."accountId")
                                 ->  Index Scan using server_pkey on server "VideoChannel->Account->Actor->Server"  (cost=0.15..0.17 rows=1 width=22) (never executed)
                                       Index Cond: (id = "VideoChannel->Account->Actor"."serverId")
                           ->  Index Scan using actor_image_actor_id_type_width on "actorImage" "VideoChannel->Account->Actor->Avatars"  (cost=0.28..0.31 rows=1 width=173) (never executed)
                                 Index Cond: (("actorId" = "VideoChannel->Account->Actor".id) AND (type = 1))
                     ->  Index Scan using user_video_history_user_id_video_id on "userVideoHistory"  (cost=0.14..0.22 rows=1 width=12) (never executed)
                           Index Cond: (("userId" = 3) AND ("videoId" = video.id))
               ->  Index Scan using video_live_video_id on "videoLive" "VideoLive"  (cost=0.14..0.21 rows=1 width=71) (never executed)
                     Index Cond: ("videoId" = video.id)
         ->  Materialize  (cost=0.00..1.00 rows=1 width=32) (never executed)
               ->  Seq Scan on "videoLiveSchedule" "VideoLive->VideoLiveSchedules"  (cost=0.00..1.00 rows=1 width=32) (never executed)
   ->  Index Scan using thumbnail_video_id on thumbnail "Thumbnails"  (cost=0.28..0.65 rows=2 width=53) (never executed)
         Index Cond: ("videoId" = video.id)
 Planning Time: 26.636 ms
 JIT:
   Functions: 138
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 41.348 ms, Inlining 34.699 ms, Optimization 3596.804 ms, Emission 1957.291 ms, Total 5630.142 ms
 Execution Time: 5642.199 ms
(91 rows)

Pas de changement à priori ?

Bon bon bon, merci @Chocobozzz pour l’aide et en fait…repasser le script de la v8 cd /var/www/peertube/peertube-latest && sudo -u peertube NODE_CONFIG_DIR=/var/www/peertube/config NODE_ENV=production node dist/scripts/migrations/peertube-8.0.js a tout résolu :white_check_mark: