refactor: noteテーブルのインデックス整理と配列カラムへのクエリでインデックスを使うように (#12993)

* Optimize note model index

* enhance(backend): ANY()をやめる (MisskeyIO#239)

* add small e2e test drive endpoint

---------

Co-authored-by: まっちゃとーにゅ <17376330+u1-liquid@users.noreply.github.com>

1 files changed, 24 insertions, 0 deletions

diff --git a/packages/backend/migration/1705222772858-optimize-note-index-for-array-column.js b/packages/backend/migration/1705222772858-optimize-note-index-for-array-column.js
new file mode 100644
index 0000000000..571bd8e8f3
--- /dev/null
+++ b/packages/backend/migration/1705222772858-optimize-note-index-for-array-column.js
@@ -0,0 +1,24 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and other misskey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class OptimizeNoteIndexForArrayColumns1705222772858 {
+    name = 'OptimizeNoteIndexForArrayColumns1705222772858'
+
+    async up(queryRunner) {
+        await queryRunner.query(`DROP INDEX "public"."IDX_796a8c03959361f97dc2be1d5c"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_54ebcb6d27222913b908d56fd8"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_88937d94d7443d9a99a76fa5c0"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_51c063b6a133a9cb87145450f5"`);
+        await queryRunner.query(`CREATE INDEX "IDX_NOTE_FILE_IDS" ON "note" using gin ("fileIds")`)
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`DROP INDEX "IDX_NOTE_FILE_IDS"`)
+        await queryRunner.query(`CREATE INDEX "IDX_51c063b6a133a9cb87145450f5" ON "note" ("fileIds") `);
+        await queryRunner.query(`CREATE INDEX "IDX_88937d94d7443d9a99a76fa5c0" ON "note" ("tags") `);
+        await queryRunner.query(`CREATE INDEX "IDX_54ebcb6d27222913b908d56fd8" ON "note" ("mentions") `);
+        await queryRunner.query(`CREATE INDEX "IDX_796a8c03959361f97dc2be1d5c" ON "note" ("visibleUserIds") `);
+    }
+}