misskey-htl.sql

explain analyse
WITH following_users AS (SELECT "followeeId" FROM "following" WHERE "followerId" = ${自分のユーザーID})
SELECT *
FROM "note"
         INNER JOIN "user" ON "user"."id" = "note"."userId"
         LEFT JOIN "note" "reply" ON "reply"."id" = "note"."replyId"
         LEFT JOIN "note" "renote" ON "renote"."id" = "note"."renoteId"
         LEFT JOIN "user" "replyUser" ON "replyUser"."id" = "reply"."userId"
         LEFT JOIN "user" "renoteUser" ON "renoteUser"."id" = "renote"."userId"
WHERE "note"."id" < ${照会範囲}
  AND "note"."channelId" IS NULL
  AND "note"."userId" IN (SELECT "followeeId" FROM following_users)
  AND ("note"."replyId" IS NULL OR ("note"."replyId" IS NOT NULL AND "note"."replyUserId" = "note"."userId"))
  AND (("note"."visibility" = 'public' OR "note"."visibility" = 'home') OR "note"."userId" = ${自分のユーザーID} OR ${自分のユーザーID} <@ "note"."visibleUserIds" OR ${自分のユーザーID} <@ "note"."mentions" OR ("note"."visibility" = 'followers' AND ("note"."userId" IN (SELECT "following"."followeeId" AS "following_followeeId" FROM "following" WHERE "following"."followerId" = ${自分のユーザーID}) OR "note"."replyUserId" = ${自分のユーザーID})))
  AND "note"."userId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" WHERE "muting"."muterId" = ${自分のユーザーID})
  AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" WHERE "muting"."muterId" = ${自分のユーザーID}))
  AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" WHERE "muting"."muterId" = ${自分のユーザーID}))
  AND ("note"."userHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" WHERE "user_profile"."userId" = ${自分のユーザーID})::jsonb ? "note"."userHost"))
  AND ("note"."replyUserHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" WHERE "user_profile"."userId" = ${自分のユーザーID})::jsonb ? "note"."replyUserHost"))
  AND ("note"."renoteUserHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" WHERE "user_profile"."userId" = ${自分のユーザーID})::jsonb ? "note"."renoteUserHost"))
  AND "note"."userId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" WHERE "blocking"."blockeeId" = ${自分のユーザーID})
  AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" WHERE "blocking"."blockeeId" = ${自分のユーザーID}))
  AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" WHERE "blocking"."blockeeId" = ${自分のユーザーID}))
  AND (("note"."renoteId" IS NOT NULL AND "note"."text" IS NULL AND "note"."userId" NOT IN (SELECT "renote_muting"."muteeId" AS "renote_muting_muteeId" FROM "renote_muting" WHERE "renote_muting"."muterId" = ${自分のユーザーID})) OR "note"."renoteId" IS NULL OR "note"."text" IS NOT NULL)
ORDER BY "note"."id" DESC
LIMIT 30
;

explain analyse
WITH following_users AS (SELECT "followeeId" FROM "following" WHERE "followerId" = ${自分のユーザーID}),
     muting_users AS (SELECT "muteeId" FROM "muting" WHERE "muterId" = ${自分のユーザーID}),
     blocking_users AS ( SELECT "blockerId" FROM "blocking" WHERE "blockeeId" = ${自分のユーザーID}),
     visible_notes AS (SELECT "id" FROM "note" WHERE "userId" = ${自分のユーザーID} OR ${自分のユーザーID} <@ "visibleUserIds" OR ${自分のユーザーID} <@ "mentions" OR "visibility" IN ('public', 'home') OR ("visibility" = 'followers' AND "userId" IN (SELECT "followeeId" FROM "following" WHERE "followerId" = ${自分のユーザーID}))),
     muted_instances AS (SELECT "mutedInstances" FROM "user_profile" WHERE "userId" = ${自分のユーザーID})
SELECT *
FROM "note"
         INNER JOIN "user" ON "user"."id" = "note"."userId"
         LEFT JOIN "note" "reply" ON "reply"."id" = "note"."replyId"
         LEFT JOIN "note" "renote" ON "renote"."id" = "note"."renoteId"
         LEFT JOIN "user" "replyUser" ON "replyUser"."id" = "reply"."userId"
         LEFT JOIN "user" "renoteUser" ON "renoteUser"."id" = "renote"."userId"
WHERE "note"."id" < ${照会範囲}
  AND "note"."channelId" IS NULL
  AND "note"."userId" IN (SELECT "followeeId" FROM following_users)
  AND ("note"."replyId" IS NULL OR "note"."replyUserId" = "note"."userId")
  AND "note"."id" IN (SELECT "id" FROM visible_notes)
  AND "note"."userId" NOT IN (SELECT "muteeId" FROM muting_users)
  AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN (SELECT "muteeId" FROM muting_users))
  AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "muteeId" FROM muting_users))
  AND ("note"."userHost" IS NULL OR NOT ((SELECT "mutedInstances" FROM muted_instances)::jsonb ? "note"."userHost"))
  AND ("note"."replyUserHost" IS NULL OR NOT ((SELECT "mutedInstances" FROM muted_instances)::jsonb ? "note"."replyUserHost"))
  AND ("note"."renoteUserHost" IS NULL OR NOT ((SELECT "mutedInstances" FROM muted_instances)::jsonb ? "note"."renoteUserHost"))
  AND "note"."userId" NOT IN (SELECT "blockerId" FROM blocking_users)
  AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN (SELECT "blockerId" FROM blocking_users))
  AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "blockerId" FROM blocking_users))
  AND (("note"."renoteId" IS NOT NULL AND "note"."text" IS NULL AND "note"."userId" NOT IN (SELECT "muteeId" FROM muting_users)) OR "note"."renoteId" IS NULL OR "note"."text" IS NOT NULL)
ORDER BY "note"."id" DESC
LIMIT 30
;