diff options
| author | 饺子w (Yumechi) <35571479+eternal-flame-AD@users.noreply.github.com> | 2025-04-23 05:29:42 +0000 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2025-04-23 14:29:42 +0900 |
| commit | 7a41cfe28bf28daa32878ff3c77790242df1e0a3 (patch) | |
| tree | c66fbe1bbbb8f051fcbddcdd0e6d4c10008c97a7 /packages/backend/migration | |
| parent | fix(deps): update dependency fastify to v5.3.2 [security] (#15866) (diff) | |
| download | sharkey-7a41cfe28bf28daa32878ff3c77790242df1e0a3.tar.gz sharkey-7a41cfe28bf28daa32878ff3c77790242df1e0a3.tar.bz2 sharkey-7a41cfe28bf28daa32878ff3c77790242df1e0a3.zip | |
enhance(backend): DB note (userId) インデクス -> (userId, id) 複合インデクスにする (#15879)
* enhance(backend): use composite index for ordering notes by user
Signed-off-by: eternal-flame-AD <yume@yumechi.jp>
* fixup! enhance(backend): use composite index for ordering notes by user
---------
Signed-off-by: eternal-flame-AD <yume@yumechi.jp>
Diffstat (limited to 'packages/backend/migration')
| -rw-r--r-- | packages/backend/migration/1745378064470-composite-note-index.js | 21 |
1 files changed, 21 insertions, 0 deletions
diff --git a/packages/backend/migration/1745378064470-composite-note-index.js b/packages/backend/migration/1745378064470-composite-note-index.js new file mode 100644 index 0000000000..49e835d38c --- /dev/null +++ b/packages/backend/migration/1745378064470-composite-note-index.js @@ -0,0 +1,21 @@ +/* + * SPDX-FileCopyrightText: syuilo and misskey-project + * SPDX-License-Identifier: AGPL-3.0-only + */ + +export class CompositeNoteIndex1745378064470 { + name = 'CompositeNoteIndex1745378064470'; + + async up(queryRunner) { + await queryRunner.query(`CREATE INDEX "IDX_724b311e6f883751f261ebe378" ON "note" ("userId", "id" DESC)`); + await queryRunner.query(`DROP INDEX IF EXISTS "IDX_5b87d9d19127bd5d92026017a7"`); + // Flush all cached Linear Scan Plans and redo statistics for composite index + // this is important for Postgres to learn that even in highly complex queries, using this index first can reduce the result set significantly + await queryRunner.query(`ANALYZE "user", "note"`); + } + + async down(queryRunner) { + await queryRunner.query(`DROP INDEX IF EXISTS "IDX_724b311e6f883751f261ebe378"`); + await queryRunner.query(`CREATE INDEX "IDX_5b87d9d19127bd5d92026017a7" ON "note" ("userId")`); + } +} |