Slow Queryを改善してほしい #11094
Labels
✨Feature
This adds/improves/enhances a feature
Comments
|
|
|
このqueryが生成されているのは生成されている箇所は 確認用コード: misskey/packages/backend/src/postgres.ts Lines 92 to 95 in 6440233 ここの import { createPostgresDataSource } from './built/postgres.js'
import { UserProfile } from './built/models/entities/UserProfile.js'
import { Following } from './built/models/entities/Following.js'
import { ChannelFollowing } from './built/models/entities/ChannelFollowing.js'
import { MutedNote } from './built/models/entities/MutedNote.js'
import { Blocking } from './built/models/entities/Blocking.js'
import { NoteThreadMuting } from './built/models/entities/NoteThreadMuting.js'
import { Muting } from './built/models/entities/Muting.js'
import { RenoteMuting } from './built/models/entities/RenoteMuting.js'
import { QueryService } from './built/core/QueryService.js'
import { Note } from './built/models/entities/Note.js'
import { SearchService} from './built/core/SearchService.js'
async function main() {
const ds = createPostgresDataSource({
db: {
host: 'db',
port: 5432,
db: 'misskey',
user: 'postgres',
pass: 'postgres',
disableCache: true,
},
})
const conn = await ds.initialize()
const upr = conn.getRepository(UserProfile)
const fr = conn.getRepository(Following)
const cfr = conn.getRepository(ChannelFollowing)
const mnr = conn.getRepository(MutedNote)
const br = conn.getRepository(Blocking)
const ntmr = conn.getRepository(NoteThreadMuting)
const mr = conn.getRepository(Muting)
const rmr = conn.getRepository(RenoteMuting)
const nr = conn.getRepository(Note)
const queryService = new QueryService(upr, fr, cfr, mnr, br, ntmr, mr, rmr)
const searchService = new SearchService({}, null, nr, queryService, null)
await searchService.searchNote('query', { id: '9grr618q4o'}, {}, {limit: 10})
}
main() |
|
全文検索が重いのは当然でどうしようもないからMeilisearch使ってもらった方がよさそう |
|
#11078 (comment) |
|
MisskeyでOneToManyを使っている箇所はなさそうなので全てのtakeをlimitに置き換えても大丈夫かしら |
|
手元で適当に 100 users * 1000 notes のDBを用意して、 大量にマッチするように DISTINCT あり (0.365 ms)SQLEXPLAIN (ANALYZE TRUE) SELECT DISTINCT "distinctAlias"."note_id" AS "ids_note_id", "distinctAlias"."note_id" FROM (SELECT "note"."id" AS "note_id", "note"."createdAt" AS "note_createdAt", "note"."replyId" AS "note_replyId", "note"."renoteId" AS "note_renoteId", "note"."threadId" AS "note_threadId", "note"."text" AS "note_text", "note"."name" AS "note_name", "note"."cw" AS "note_cw", "note"."userId" AS "note_userId", "note"."localOnly" AS "note_localOnly", "note"."reactionAcceptance" AS "note_reactionAcceptance", "note"."renoteCount" AS "note_renoteCount", "note"."repliesCount" AS "note_repliesCount", "note"."reactions" AS "note_reactions", "note"."visibility" AS "note_visibility", "note"."uri" AS "note_uri", "note"."url" AS "note_url", "note"."fileIds" AS "note_fileIds", "note"."attachedFileTypes" AS "note_attachedFileTypes", "note"."visibleUserIds" AS "note_visibleUserIds", "note"."mentions" AS "note_mentions", "note"."mentionedRemoteUsers" AS "note_mentionedRemoteUsers", "note"."emojis" AS "note_emojis", "note"."tags" AS "note_tags", "note"."hasPoll" AS "note_hasPoll", "note"."channelId" AS "note_channelId", "note"."userHost" AS "note_userHost", "note"."replyUserId" AS "note_replyUserId", "note"."replyUserHost" AS "note_replyUserHost", "note"."renoteUserId" AS "note_renoteUserId", "note"."renoteUserHost" AS "note_renoteUserHost", "user"."id" AS "user_id", "user"."createdAt" AS "user_createdAt", "user"."updatedAt" AS "user_updatedAt", "user"."lastFetchedAt" AS "user_lastFetchedAt", "user"."lastActiveDate" AS "user_lastActiveDate", "user"."hideOnlineStatus" AS "user_hideOnlineStatus", "user"."username" AS "user_username", "user"."name" AS "user_name", "user"."followersCount" AS "user_followersCount", "user"."followingCount" AS "user_followingCount", "user"."movedToUri" AS "user_movedToUri", "user"."movedAt" AS "user_movedAt", "user"."alsoKnownAs" AS "user_alsoKnownAs", "user"."notesCount" AS "user_notesCount", "user"."avatarId" AS "user_avatarId", "user"."bannerId" AS "user_bannerId", "user"."avatarUrl" AS "user_avatarUrl", "user"."bannerUrl" AS "user_bannerUrl", "user"."avatarBlurhash" AS "user_avatarBlurhash", "user"."bannerBlurhash" AS "user_bannerBlurhash", "user"."tags" AS "user_tags", "user"."isSuspended" AS "user_isSuspended", "user"."isLocked" AS "user_isLocked", "user"."isBot" AS "user_isBot", "user"."isCat" AS "user_isCat", "user"."isRoot" AS "user_isRoot", "user"."isExplorable" AS "user_isExplorable", "user"."isDeleted" AS "user_isDeleted", "user"."emojis" AS "user_emojis", "user"."host" AS "user_host", "user"."inbox" AS "user_inbox", "user"."sharedInbox" AS "user_sharedInbox", "user"."featured" AS "user_featured", "user"."uri" AS "user_uri", "user"."followersUri" AS "user_followersUri", "user"."token" AS "user_token", "reply"."id" AS "reply_id", "reply"."createdAt" AS "reply_createdAt", "reply"."replyId" AS "reply_replyId", "reply"."renoteId" AS "reply_renoteId", "reply"."threadId" AS "reply_threadId", "reply"."text" AS "reply_text", "reply"."name" AS "reply_name", "reply"."cw" AS "reply_cw", "reply"."userId" AS "reply_userId", "reply"."localOnly" AS "reply_localOnly", "reply"."reactionAcceptance" AS "reply_reactionAcceptance", "reply"."renoteCount" AS "reply_renoteCount", "reply"."repliesCount" AS "reply_repliesCount", "reply"."reactions" AS "reply_reactions", "reply"."visibility" AS "reply_visibility", "reply"."uri" AS "reply_uri", "reply"."url" AS "reply_url", "reply"."fileIds" AS "reply_fileIds", "reply"."attachedFileTypes" AS "reply_attachedFileTypes", "reply"."visibleUserIds" AS "reply_visibleUserIds", "reply"."mentions" AS "reply_mentions", "reply"."mentionedRemoteUsers" AS "reply_mentionedRemoteUsers", "reply"."emojis" AS "reply_emojis", "reply"."tags" AS "reply_tags",
"reply"."hasPoll" AS "reply_hasPoll", "reply"."channelId" AS "reply_channelId", "reply"."userHost" AS "reply_userHost", "reply"."replyUserId" AS "reply_replyUserId", "reply"."replyUserHost" AS "reply_replyUserHost", "reply"."renoteUserId" AS "reply_renoteUserId", "reply"."renoteUserHost" AS "reply_renoteUserHost", "renote"."id" AS "renote_id", "renote"."createdAt" AS "renote_createdAt", "renote"."replyId" AS "renote_replyId", "renote"."renoteId" AS "renote_renoteId", "renote"."threadId" AS "renote_threadId", "renote"."text" AS "renote_text", "renote"."name" AS "renote_name", "renote"."cw" AS "renote_cw", "renote"."userId" AS "renote_userId", "renote"."localOnly" AS "renote_localOnly", "renote"."reactionAcceptance" AS "renote_reactionAcceptance", "renote"."renoteCount" AS "renote_renoteCount", "renote"."repliesCount" AS "renote_repliesCount", "renote"."reactions" AS "renote_reactions", "renote"."visibility" AS "renote_visibility", "renote"."uri" AS "renote_uri", "renote"."url" AS "renote_url", "renote"."fileIds" AS "renote_fileIds", "renote"."attachedFileTypes" AS "renote_attachedFileTypes", "renote"."visibleUserIds" AS "renote_visibleUserIds", "renote"."mentions" AS "renote_mentions", "renote"."mentionedRemoteUsers" AS "renote_mentionedRemoteUsers", "renote"."emojis" AS "renote_emojis", "renote"."tags" AS "renote_tags", "renote"."hasPoll" AS "renote_hasPoll", "renote"."channelId" AS "renote_channelId", "renote"."userHost" AS "renote_userHost", "renote"."replyUserId" AS "renote_replyUserId", "renote"."replyUserHost" AS "renote_replyUserHost", "renote"."renoteUserId" AS "renote_renoteUserId", "renote"."renoteUserHost" AS "renote_renoteUserHost", "replyUser"."id" AS "replyUser_id", "replyUser"."createdAt" AS "replyUser_createdAt", "replyUser"."updatedAt" AS "replyUser_updatedAt", "replyUser"."lastFetchedAt" AS "replyUser_lastFetchedAt", "replyUser"."lastActiveDate" AS "replyUser_lastActiveDate", "replyUser"."hideOnlineStatus" AS "replyUser_hideOnlineStatus", "replyUser"."username" AS "replyUser_username", "replyUser"."name" AS "replyUser_name", "replyUser"."followersCount" AS "replyUser_followersCount", "replyUser"."followingCount" AS "replyUser_followingCount",
"replyUser"."movedToUri" AS "replyUser_movedToUri", "replyUser"."movedAt" AS "replyUser_movedAt", "replyUser"."alsoKnownAs" AS "replyUser_alsoKnownAs", "replyUser"."notesCount" AS "replyUser_notesCount", "replyUser"."avatarId" AS "replyUser_avatarId", "replyUser"."bannerId" AS "replyUser_bannerId", "replyUser"."avatarUrl" AS "replyUser_avatarUrl", "replyUser"."bannerUrl" AS "replyUser_bannerUrl", "replyUser"."avatarBlurhash" AS "replyUser_avatarBlurhash", "replyUser"."bannerBlurhash" AS "replyUser_bannerBlurhash", "replyUser"."tags" AS "replyUser_tags", "replyUser"."isSuspended" AS "replyUser_isSuspended", "replyUser"."isLocked" AS "replyUser_isLocked", "replyUser"."isBot" AS "replyUser_isBot", "replyUser"."isCat" AS "replyUser_isCat", "replyUser"."isRoot" AS "replyUser_isRoot", "replyUser"."isExplorable" AS "replyUser_isExplorable", "replyUser"."isDeleted" AS "replyUser_isDeleted", "replyUser"."emojis" AS "replyUser_emojis", "replyUser"."host" AS "replyUser_host", "replyUser"."inbox" AS "replyUser_inbox", "replyUser"."sharedInbox" AS "replyUser_sharedInbox", "replyUser"."featured"
AS "replyUser_featured", "replyUser"."uri" AS "replyUser_uri", "replyUser"."followersUri" AS "replyUser_followersUri", "replyUser"."token" AS "replyUser_token", "renoteUser"."id" AS "renoteUser_id", "renoteUser"."createdAt" AS "renoteUser_createdAt", "renoteUser"."updatedAt" AS "renoteUser_updatedAt", "renoteUser"."lastFetchedAt" AS "renoteUser_lastFetchedAt", "renoteUser"."lastActiveDate" AS "renoteUser_lastActiveDate", "renoteUser"."hideOnlineStatus" AS "renoteUser_hideOnlineStatus", "renoteUser"."username" AS "renoteUser_username", "renoteUser"."name" AS "renoteUser_name", "renoteUser"."followersCount" AS "renoteUser_followersCount", "renoteUser"."followingCount" AS "renoteUser_followingCount", "renoteUser"."movedToUri" AS "renoteUser_movedToUri", "renoteUser"."movedAt" AS "renoteUser_movedAt", "renoteUser"."alsoKnownAs" AS "renoteUser_alsoKnownAs", "renoteUser"."notesCount" AS "renoteUser_notesCount", "renoteUser"."avatarId" AS "renoteUser_avatarId", "renoteUser"."bannerId" AS "renoteUser_bannerId", "renoteUser"."avatarUrl" AS "renoteUser_avatarUrl", "renoteUser"."bannerUrl" AS
"renoteUser_bannerUrl", "renoteUser"."avatarBlurhash" AS "renoteUser_avatarBlurhash", "renoteUser"."bannerBlurhash" AS "renoteUser_bannerBlurhash", "renoteUser"."tags" AS "renoteUser_tags", "renoteUser"."isSuspended" AS "renoteUser_isSuspended", "renoteUser"."isLocked" AS "renoteUser_isLocked", "renoteUser"."isBot" AS "renoteUser_isBot", "renoteUser"."isCat" AS "renoteUser_isCat", "renoteUser"."isRoot" AS "renoteUser_isRoot", "renoteUser"."isExplorable" AS "renoteUser_isExplorable", "renoteUser"."isDeleted" AS "renoteUser_isDeleted", "renoteUser"."emojis" AS "renoteUser_emojis", "renoteUser"."host" AS "renoteUser_host",
"renoteUser"."inbox" AS "renoteUser_inbox", "renoteUser"."sharedInbox" AS "renoteUser_sharedInbox", "renoteUser"."featured" AS "renoteUser_featured", "renoteUser"."uri" AS "renoteUser_uri", "renoteUser"."followersUri" AS "renoteUser_followersUri", "renoteUser"."token" AS "renoteUser_token" FROM "note" "note" INNER JOIN "user" "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"."visibility" = 'public' OR "note"."visibility" = 'home') OR "note"."userId" = 'u0000000000' OR 'u0000000000' = ANY("note"."visibleUserIds") OR 'u0000000000' = ANY("note"."mentions") OR ("note"."visibility" = 'followers' AND ("note"."userId" IN (SELECT "following"."followeeId" AS "following_followeeId" FROM "following" "following" WHERE "following"."followerId" = 'u0000000000') OR "note"."replyUserId" = 'u0000000000'))) AND "note"."userId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" "muting" WHERE "muting"."muterId" = 'u0000000000') AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN
(SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" "muting" WHERE "muting"."muterId" = 'u0000000000')) AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" "muting" WHERE "muting"."muterId" = 'u0000000000')) AND ("note"."userHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" "user_profile" WHERE "user_profile"."userId" = 'u0000000000')::jsonb ? "note"."userHost")) AND ("note"."replyUserHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" "user_profile" WHERE "user_profile"."userId" = 'u0000000000')::jsonb ? "note"."replyUserHost")) AND ("note"."renoteUserHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" "user_profile" WHERE "user_profile"."userId" = 'u0000000000')::jsonb ? "note"."renoteUserHost")) AND "note"."userId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" "blocking" WHERE "blocking"."blockeeId" = 'u0000000000') AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" "blocking" WHERE "blocking"."blockeeId" = 'u0000000000')) AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" "blocking" WHERE "blocking"."blockeeId" = 'u0000000000'))) "distinctAlias" ORDER BY "distinctAlias"."note_id" DESC, "note_id" ASC LIMIT 10;explain 結果 Limit (cost=97.61..106.60 rows=10 width=44) (actual time=0.161..0.173 rows=10 loops=1)
InitPlan 5 (returns $4)
-> Index Scan using "UQ_51cb79b5555effaf7d69ba1cff9" on user_profile (cost=0.14..8.15 rows=1 width=32) (never executed)
Index Cond: (("userId")::text = 'u0000000000'::text)
InitPlan 6 (returns $5)
-> Index Scan using "UQ_51cb79b5555effaf7d69ba1cff9" on user_profile user_profile_1 (cost=0.14..8.15 rows=1 width=32) (never executed)
Index Cond: (("userId")::text = 'u0000000000'::text)
InitPlan 7 (returns $6)
-> Index Scan using "UQ_51cb79b5555effaf7d69ba1cff9" on user_profile user_profile_2 (cost=0.14..8.15 rows=1 width=32) (never executed)
Index Cond: (("userId")::text = 'u0000000000'::text)
-> Unique (cost=73.15..27048.49 rows=30006 width=44) (actual time=0.160..0.171 rows=10 loops=1)
-> Nested Loop (cost=73.15..26973.47 rows=30006 width=44) (actual time=0.160..0.169 rows=10 loops=1)
-> Index Scan Backward using "PK_96d0c172a4fba276b1bbed43058" on note (cost=73.00..26212.60 rows=30006 width=138) (actual time=0.144..0.149 rows=10 loops=1)
Filter: ((NOT (hashed SubPlan 2)) AND (("replyUserId" IS NULL) OR (NOT (hashed SubPlan 3))) AND (("renoteUserId" IS NULL) OR (NOT (hashed SubPlan 4))) AND (("userHost" IS NULL) OR (NOT ($4 ? ("userHost")::text))) AND (("replyUserHost" IS NULL) OR (NOT ($5 ? ("replyUserHost")::text))) AND (("renoteUserHost" IS NULL) OR (NOT ($6 ? ("renoteUserHost")::text))) AND (NOT (hashed SubPlan 8)) AND (("replyUserId" IS NULL) OR (NOT (hashed SubPlan 9))) AND (("renoteUserId" IS NULL) OR (NOT (hashed SubPlan 10))) AND ((visibility = 'public'::note_visibility_enum) OR (visibility = 'home'::note_visibility_enum) OR (("userId")::text = 'u0000000000'::text) OR ('u0000000000'::text = ANY (("visibleUserIds")::text[])) OR ('u0000000000'::text = ANY ((mentions)::text[])) OR ((visibility = 'followers'::note_visibility_enum) AND ((hashed SubPlan 1) OR (("replyUserId")::text = 'u0000000000'::text)))))
Rows Removed by Filter: 100
SubPlan 2
-> Index Only Scan using "IDX_1eb9d9824a630321a29fd3b290" on muting (cost=0.28..4.49 rows=12 width=12) (actual time=0.025..0.032 rows=12 loops=1)
Index Cond: ("muterId" = 'u0000000000'::text)
Heap Fetches: 7
SubPlan 3
-> Index Only Scan using "IDX_1eb9d9824a630321a29fd3b290" on muting muting_1 (cost=0.28..4.49 rows=12 width=12) (never executed)
Index Cond: ("muterId" = 'u0000000000'::text)
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using "IDX_1eb9d9824a630321a29fd3b290" on muting muting_2 (cost=0.28..4.49 rows=12 width=12) (never executed)
Index Cond: ("muterId" = 'u0000000000'::text)
Heap Fetches: 0
SubPlan 8
-> Index Scan using "IDX_2cd4a2743a99671308f5417759" on blocking (cost=0.28..17.81 rows=13 width=12) (actual time=0.011..0.013 rows=13 loops=1)
Index Cond: (("blockeeId")::text = 'u0000000000'::text)
SubPlan 9
-> Index Scan using "IDX_2cd4a2743a99671308f5417759" on blocking blocking_1 (cost=0.28..17.81 rows=13 width=12) (never executed)
Index Cond: (("blockeeId")::text = 'u0000000000'::text)
SubPlan 10
-> Index Scan using "IDX_2cd4a2743a99671308f5417759" on blocking blocking_2 (cost=0.28..17.81 rows=13 width=12) (never executed)
Index Cond: (("blockeeId")::text = 'u0000000000'::text)
SubPlan 1
-> Index Only Scan using "IDX_307be5f1d1252e0388662acb96" on following (cost=0.28..5.37 rows=62 width=12) (never executed)
Index Cond: ("followerId" = 'u0000000000'::text)
Heap Fetches: 0
-> Memoize (cost=0.15..0.17 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=10)
Cache Key: note."userId"
Cache Mode: logical
Hits: 9 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Only Scan using "PK_cace4a159ff9f2512dd42373760" on "user" (cost=0.14..0.16 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (id = (note."userId")::text)
Heap Fetches: 0
Planning Time: 7.485 ms
Execution Time: 0.365 ms(54 rows)DISTINCT なし (0.894 ms)SQLEXPLAIN (ANALYZE TRUE) SELECT "note"."id" AS "note_id", "note"."createdAt" AS "note_createdAt", "note"."replyId" AS "note_replyId", "note"."renoteId" AS "note_renoteId", "note"."threadId" AS "note_threadId", "note"."text" AS "note_text", "note"."name" AS "note_name", "note"."cw" AS "note_cw", "note"."userId" AS "note_userId", "note"."localOnly" AS "note_localOnly", "note"."reactionAcceptance" AS "note_reactionAcceptance", "note"."renoteCount" AS "note_renoteCount", "note"."repliesCount" AS "note_repliesCount", "note"."reactions" AS "note_reactions", "note"."visibility" AS "note_visibility", "note"."uri" AS "note_uri", "note"."url" AS "note_url", "note"."fileIds" AS "note_fileIds", "note"."attachedFileTypes" AS "note_attachedFileTypes", "note"."visibleUserIds" AS "note_visibleUserIds", "note"."mentions" AS "note_mentions", "note"."mentionedRemoteUsers" AS "note_mentionedRemoteUsers", "note"."emojis" AS "note_emojis", "note"."tags" AS "note_tags", "note"."hasPoll" AS "note_hasPoll", "note"."channelId" AS "note_channelId", "note"."userHost" AS "note_userHost", "note"."replyUserId" AS "note_replyUserId", "note"."replyUserHost" AS "note_replyUserHost", "note"."renoteUserId" AS "note_renoteUserId", "note"."renoteUserHost" AS "note_renoteUserHost", "user"."id" AS "user_id", "user"."createdAt" AS "user_createdAt", "user"."updatedAt" AS "user_updatedAt", "user"."lastFetchedAt" AS "user_lastFetchedAt", "user"."lastActiveDate" AS "user_lastActiveDate", "user"."hideOnlineStatus" AS "user_hideOnlineStatus", "user"."username" AS "user_username", "user"."name" AS "user_name", "user"."followersCount" AS "user_followersCount", "user"."followingCount" AS "user_followingCount", "user"."movedToUri" AS "user_movedToUri", "user"."movedAt" AS "user_movedAt", "user"."alsoKnownAs" AS "user_alsoKnownAs", "user"."notesCount" AS "user_notesCount", "user"."avatarId" AS "user_avatarId", "user"."bannerId" AS "user_bannerId", "user"."avatarUrl" AS "user_avatarUrl", "user"."bannerUrl" AS "user_bannerUrl", "user"."avatarBlurhash" AS "user_avatarBlurhash", "user"."bannerBlurhash" AS "user_bannerBlurhash", "user"."tags" AS "user_tags", "user"."isSuspended" AS "user_isSuspended", "user"."isLocked" AS "user_isLocked", "user"."isBot" AS "user_isBot", "user"."isCat" AS "user_isCat", "user"."isRoot" AS "user_isRoot", "user"."isExplorable" AS "user_isExplorable", "user"."isDeleted" AS "user_isDeleted", "user"."emojis" AS "user_emojis", "user"."host" AS "user_host", "user"."inbox" AS "user_inbox", "user"."sharedInbox" AS "user_sharedInbox", "user"."featured" AS "user_featured", "user"."uri" AS "user_uri", "user"."followersUri" AS "user_followersUri", "user"."token" AS "user_token", "reply"."id" AS "reply_id", "reply"."createdAt" AS "reply_createdAt", "reply"."replyId" AS "reply_replyId", "reply"."renoteId" AS "reply_renoteId", "reply"."threadId" AS "reply_threadId", "reply"."text" AS "reply_text", "reply"."name" AS "reply_name", "reply"."cw" AS "reply_cw", "reply"."userId" AS "reply_userId", "reply"."localOnly" AS "reply_localOnly", "reply"."reactionAcceptance" AS "reply_reactionAcceptance", "reply"."renoteCount" AS "reply_renoteCount", "reply"."repliesCount" AS "reply_repliesCount", "reply"."reactions" AS "reply_reactions", "reply"."visibility" AS "reply_visibility", "reply"."uri" AS "reply_uri", "reply"."url" AS "reply_url", "reply"."fileIds" AS "reply_fileIds", "reply"."attachedFileTypes" AS "reply_attachedFileTypes", "reply"."visibleUserIds" AS "reply_visibleUserIds", "reply"."mentions" AS "reply_mentions", "reply"."mentionedRemoteUsers" AS "reply_mentionedRemoteUsers", "reply"."emojis" AS "reply_emojis", "reply"."tags" AS "reply_tags", "reply"."hasPoll" AS "reply_hasPoll", "reply"."channelId" AS "reply_channelId", "reply"."userHost" AS "reply_userHost", "reply"."replyUserId" AS "reply_replyUserId", "reply"."replyUserHost" AS "reply_replyUserHost", "reply"."renoteUserId" AS "reply_renoteUserId", "reply"."renoteUserHost" AS "reply_renoteUserHost", "renote"."id" AS "renote_id", "renote"."createdAt" AS "renote_createdAt", "renote"."replyId" AS "renote_replyId", "renote"."renoteId" AS "renote_renoteId", "renote"."threadId" AS "renote_threadId", "renote"."text" AS "renote_text", "renote"."name" AS "renote_name", "renote"."cw" AS "renote_cw", "renote"."userId" AS "renote_userId", "renote"."localOnly" AS "renote_localOnly", "renote"."reactionAcceptance" AS "renote_reactionAcceptance", "renote"."renoteCount" AS "renote_renoteCount", "renote"."repliesCount" AS "renote_repliesCount", "renote"."reactions" AS "renote_reactions", "renote"."visibility" AS "renote_visibility", "renote"."uri" AS "renote_uri", "renote"."url" AS "renote_url", "renote"."fileIds" AS "renote_fileIds", "renote"."attachedFileTypes" AS "renote_attachedFileTypes", "renote"."visibleUserIds" AS "renote_visibleUserIds", "renote"."mentions" AS "renote_mentions", "renote"."mentionedRemoteUsers" AS "renote_mentionedRemoteUsers", "renote"."emojis" AS "renote_emojis", "renote"."tags" AS "renote_tags", "renote"."hasPoll" AS "renote_hasPoll", "renote"."channelId" AS "renote_channelId", "renote"."userHost" AS "renote_userHost", "renote"."replyUserId" AS "renote_replyUserId", "renote"."replyUserHost" AS "renote_replyUserHost", "renote"."renoteUserId" AS "renote_renoteUserId", "renote"."renoteUserHost" AS "renote_renoteUserHost", "replyUser"."id" AS "replyUser_id", "replyUser"."createdAt" AS "replyUser_createdAt", "replyUser"."updatedAt" AS "replyUser_updatedAt", "replyUser"."lastFetchedAt" AS "replyUser_lastFetchedAt", "replyUser"."lastActiveDate" AS "replyUser_lastActiveDate", "replyUser"."hideOnlineStatus" AS "replyUser_hideOnlineStatus", "replyUser"."username" AS "replyUser_username", "replyUser"."name" AS "replyUser_name", "replyUser"."followersCount" AS "replyUser_followersCount", "replyUser"."followingCount" AS "replyUser_followingCount", "replyUser"."movedToUri" AS "replyUser_movedToUri", "replyUser"."movedAt" AS "replyUser_movedAt", "replyUser"."alsoKnownAs" AS "replyUser_alsoKnownAs", "replyUser"."notesCount" AS "replyUser_notesCount", "replyUser"."avatarId" AS "replyUser_avatarId", "replyUser"."bannerId" AS "replyUser_bannerId", "replyUser"."avatarUrl" AS "replyUser_avatarUrl", "replyUser"."bannerUrl" AS "replyUser_bannerUrl", "replyUser"."avatarBlurhash" AS "replyUser_avatarBlurhash", "replyUser"."bannerBlurhash" AS "replyUser_bannerBlurhash", "replyUser"."tags" AS "replyUser_tags", "replyUser"."isSuspended" AS "replyUser_isSuspended", "replyUser"."isLocked" AS "replyUser_isLocked", "replyUser"."isBot" AS "replyUser_isBot", "replyUser"."isCat" AS "replyUser_isCat", "replyUser"."isRoot" AS "replyUser_isRoot", "replyUser"."isExplorable" AS "replyUser_isExplorable", "replyUser"."isDeleted" AS "replyUser_isDeleted", "replyUser"."emojis" AS "replyUser_emojis", "replyUser"."host" AS "replyUser_host", "replyUser"."inbox" AS "replyUser_inbox", "replyUser"."sharedInbox" AS "replyUser_sharedInbox", "replyUser"."featured" AS "replyUser_featured", "replyUser"."uri" AS "replyUser_uri", "replyUser"."followersUri" AS "replyUser_followersUri", "replyUser"."token" AS "replyUser_token", "renoteUser"."id" AS "renoteUser_id", "renoteUser"."createdAt" AS "renoteUser_createdAt", "renoteUser"."updatedAt" AS "renoteUser_updatedAt", "renoteUser"."lastFetchedAt" AS "renoteUser_lastFetchedAt", "renoteUser"."lastActiveDate" AS "renoteUser_lastActiveDate", "renoteUser"."hideOnlineStatus" AS "renoteUser_hideOnlineStatus", "renoteUser"."username" AS "renoteUser_username", "renoteUser"."name" AS "renoteUser_name", "renoteUser"."followersCount" AS "renoteUser_followersCount", "renoteUser"."followingCount" AS "renoteUser_followingCount", "renoteUser"."movedToUri" AS "renoteUser_movedToUri", "renoteUser"."movedAt" AS "renoteUser_movedAt", "renoteUser"."alsoKnownAs" AS "renoteUser_alsoKnownAs", "renoteUser"."notesCount" AS "renoteUser_notesCount", "renoteUser"."avatarId" AS "renoteUser_avatarId", "renoteUser"."bannerId" AS "renoteUser_bannerId", "renoteUser"."avatarUrl" AS "renoteUser_avatarUrl", "renoteUser"."bannerUrl" AS "renoteUser_bannerUrl", "renoteUser"."avatarBlurhash" AS "renoteUser_avatarBlurhash", "renoteUser"."bannerBlurhash" AS "renoteUser_bannerBlurhash", "renoteUser"."tags" AS "renoteUser_tags", "renoteUser"."isSuspended" AS "renoteUser_isSuspended", "renoteUser"."isLocked" AS "renoteUser_isLocked", "renoteUser"."isBot" AS "renoteUser_isBot", "renoteUser"."isCat" AS "renoteUser_isCat", "renoteUser"."isRoot" AS "renoteUser_isRoot", "renoteUser"."isExplorable" AS "renoteUser_isExplorable", "renoteUser"."isDeleted" AS "renoteUser_isDeleted", "renoteUser"."emojis" AS "renoteUser_emojis", "renoteUser"."host" AS "renoteUser_host", "renoteUser"."inbox" AS "renoteUser_inbox", "renoteUser"."sharedInbox" AS "renoteUser_sharedInbox", "renoteUser"."featured" AS "renoteUser_featured", "renoteUser"."uri" AS "renoteUser_uri", "renoteUser"."followersUri" AS "renoteUser_followersUri", "renoteUser"."token" AS "renoteUser_token" FROM "note" "note" INNER JOIN "user" "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"."visibility" = 'public' OR "note"."visibility" = 'home') OR "note"."userId" = 'u0000000000' OR 'u0000000000' = ANY("note"."visibleUserIds") OR 'u0000000000' = ANY("note"."mentions") OR ("note"."visibility" = 'followers' AND ("note"."userId" IN (SELECT "following"."followeeId" AS "following_followeeId" FROM "following" "following" WHERE "following"."followerId" = 'u0000000000') OR "note"."replyUserId" = 'u0000000000'))) AND "note"."userId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" "muting" WHERE "muting"."muterId" = 'u0000000000') AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" "muting" WHERE "muting"."muterId" = 'u0000000000')) AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "muting"."muteeId" AS "muting_muteeId" FROM "muting" "muting" WHERE "muting"."muterId" = 'u0000000000')) AND ("note"."userHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" "user_profile" WHERE "user_profile"."userId" = 'u0000000000')::jsonb ? "note"."userHost")) AND ("note"."replyUserHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" "user_profile" WHERE "user_profile"."userId" = 'u0000000000')::jsonb ? "note"."replyUserHost")) AND ("note"."renoteUserHost" IS NULL OR NOT ((SELECT "user_profile"."mutedInstances" AS "user_profile_mutedInstances" FROM "user_profile" "user_profile" WHERE "user_profile"."userId" = 'u0000000000')::jsonb ? "note"."renoteUserHost")) AND "note"."userId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" "blocking" WHERE "blocking"."blockeeId" = 'u0000000000') AND ("note"."replyUserId" IS NULL OR "note"."replyUserId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" "blocking" WHERE "blocking"."blockeeId" = 'u0000000000')) AND ("note"."renoteUserId" IS NULL OR "note"."renoteUserId" NOT IN (SELECT "blocking"."blockerId" AS "blocking_blockerId" FROM "blocking" "blocking" WHERE "blocking"."blockeeId" = 'u0000000000')) ORDER BY "note"."id" DESC LIMIT 10;explain 結果 Limit (cost=98.75..133.21 rows=10 width=28578) (actual time=0.241..0.432 rows=10 loops=1)
InitPlan 5 (returns $4)
-> Index Scan using "UQ_51cb79b5555effaf7d69ba1cff9" on user_profile (cost=0.14..8.15 rows=1 width=32) (never executed)
Index Cond: (("userId")::text = 'u0000000000'::text)
InitPlan 6 (returns $5)
-> Index Scan using "UQ_51cb79b5555effaf7d69ba1cff9" on user_profile user_profile_1 (cost=0.14..8.15 rows=1 width=32) (never executed)
Index Cond: (("userId")::text = 'u0000000000'::text)
InitPlan 7 (returns $6)
-> Index Scan using "UQ_51cb79b5555effaf7d69ba1cff9" on user_profile user_profile_2 (cost=0.14..8.15 rows=1 width=32) (never executed)
Index Cond: (("userId")::text = 'u0000000000'::text)
-> Nested Loop Left Join (cost=74.29..103473.21 rows=30006 width=28578) (actual time=0.240..0.430 rows=10 loops=1)
-> Nested Loop Left Join (cost=74.14..102712.40 rows=30006 width=23238) (actual time=0.210..0.392 rows=10 loops=1)
-> Nested Loop Left Join (cost=73.99..101951.60 rows=30006 width=17898) (actual time=0.199..0.329 rows=10 loops=1)
-> Nested Loop Left Join (cost=73.57..64462.99 rows=30006 width=13712) (actual time=0.161..0.181 rows=10 loops=1)
-> Nested Loop (cost=73.15..26973.55 rows=30006 width=9526) (actual time=0.153..0.168 rows=10 loops=1)
-> Index Scan Backward using "PK_96d0c172a4fba276b1bbed43058" on note (cost=73.00..26212.60 rows=30006 width=4186) (actual time=0.139..0.146 rows=10 loops=1)
Filter: ((NOT (hashed SubPlan 2)) AND (("replyUserId" IS NULL) OR (NOT (hashed SubPlan 3))) AND (("renoteUserId" IS NULL) OR (NOT (hashed SubPlan 4))) AND (("userHost" IS NULL) OR (NOT ($4 ? ("userHost")::text))) AND (("replyUserHost" IS NULL) OR (NOT ($5 ? ("replyUserHost")::text))) AND (("renoteUserHost" IS NULL) OR (NOT ($6 ? ("renoteUserHost")::text))) AND (NOT (hashed SubPlan 8)) AND (("replyUserId" IS NULL) OR (NOT (hashed SubPlan 9))) AND (("renoteUserId" IS NULL) OR (NOT (hashed SubPlan 10))) AND ((visibility = 'public'::note_visibility_enum) OR (visibility = 'home'::note_visibility_enum) OR (("userId")::text = 'u0000000000'::text) OR ('u0000000000'::text = ANY (("visibleUserIds")::text[])) OR ('u0000000000'::text = ANY ((mentions)::text[])) OR ((visibility = 'followers'::note_visibility_enum) AND ((hashed SubPlan 1) OR (("replyUserId")::text = 'u0000000000'::text)))))
Rows Removed by Filter: 100
SubPlan 2
-> Index Only Scan using "IDX_1eb9d9824a630321a29fd3b290" on muting (cost=0.28..4.49 rows=12 width=12) (actual time=0.024..0.032 rows=12 loops=1)
Index Cond: ("muterId" = 'u0000000000'::text)
Heap Fetches: 7
SubPlan 3
-> Index Only Scan using "IDX_1eb9d9824a630321a29fd3b290" on muting muting_1 (cost=0.28..4.49 rows=12 width=12) (never executed)
Index Cond: ("muterId" = 'u0000000000'::text)
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using "IDX_1eb9d9824a630321a29fd3b290" on muting muting_2 (cost=0.28..4.49 rows=12 width=12) (never executed)
Index Cond: ("muterId" = 'u0000000000'::text)
Heap Fetches: 0
SubPlan 8
-> Index Scan using "IDX_2cd4a2743a99671308f5417759" on blocking (cost=0.28..17.81 rows=13 width=12) (actual time=0.010..0.013 rows=13 loops=1)
Index Cond: (("blockeeId")::text = 'u0000000000'::text)
SubPlan 9
-> Index Scan using "IDX_2cd4a2743a99671308f5417759" on blocking blocking_1 (cost=0.28..17.81 rows=13 width=12) (never executed)
Index Cond: (("blockeeId")::text = 'u0000000000'::text)
SubPlan 10
-> Index Scan using "IDX_2cd4a2743a99671308f5417759" on blocking blocking_2 (cost=0.28..17.81 rows=13 width=12) (never executed)
Index Cond: (("blockeeId")::text = 'u0000000000'::text)
SubPlan 1
-> Index Only Scan using "IDX_307be5f1d1252e0388662acb96" on following (cost=0.28..5.37 rows=62 width=12) (never executed)
Index Cond: ("followerId" = 'u0000000000'::text)
Heap Fetches: 0
-> Memoize (cost=0.15..0.17 rows=1 width=5340) (actual time=0.001..0.001 rows=1 loops=10)
Cache Key: note."userId"
Cache Mode: logical
Hits: 9 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_cace4a159ff9f2512dd42373760" on "user" (cost=0.14..0.16 rows=1 width=5340) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: ((id)::text = (note."userId")::text)
-> Index Scan using "PK_96d0c172a4fba276b1bbed43058" on note reply (cost=0.42..1.25 rows=1 width=4186) (actual time=0.000..0.000 rows=0 loops=10)
Index Cond: ((id)::text = (note."replyId")::text)
-> Index Scan using "PK_96d0c172a4fba276b1bbed43058" on note renote (cost=0.42..1.25 rows=1 width=4186) (actual time=0.004..0.004 rows=1 loops=10)
Index Cond: ((id)::text = (note."renoteId")::text)
-> Memoize (cost=0.15..0.17 rows=1 width=5340) (actual time=0.004..0.004 rows=0 loops=10)
Cache Key: reply."userId"
Cache Mode: logical
Hits: 9 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_cace4a159ff9f2512dd42373760" on "user" "replyUser" (cost=0.14..0.16 rows=1 width=5340) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((id)::text = (reply."userId")::text)
-> Memoize (cost=0.15..0.17 rows=1 width=5340) (actual time=0.002..0.002 rows=1 loops=10)
Cache Key: renote."userId"
Cache Mode: logical
Hits: 9 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using "PK_cace4a159ff9f2512dd42373760" on "user" "renoteUser" (cost=0.14..0.16 rows=1 width=5340) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: ((id)::text = (renote."userId")::text)
Planning Time: 10.110 ms
Execution Time: 0.894 ms |
|
ORDER BYがインデックスが適切でないとダメらしい（知らんけど |
This comment was marked as off-topic.
This comment was marked as off-topic.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Summary
下記なslow queryを改善してほしいです
上記のqueryはこのコード付近で生成されていると思われます
misskey/packages/backend/src/core/chart/charts/federation.ts
Lines 60 to 100 in 84d3a06
The text was updated successfully, but these errors were encountered: