ドライブから"添付されているノート"閲覧で"note"をフルスキャンするのを解消する

[yszkst]

💡 Summary

主題

ファイルが添付されているノートを検索するクエリが"note"をフルスキャンする

解消策

• note.fileIds についているINDEXがbtreeなのでGINにする
• クエリを :file = ANY(note.fileIds) から note.fileIds @> '{ファイルID}' にする
  • packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts
  • GIN で効く演算子 <@ @> = &&
    • https://www.postgresql.jp/document/15/html/indexes-types.html

影響

• fileIds に対するクエリで Where('note.fileIds != \'{}\''); の個所は btree でも GIN でもインデックスが効かないので無影響
• packages/backend/src/server/api/endpoints/notes.ts L74 に = {} がありこれは btree で有効になる
  • あえて "ファイル無しノート" を取得することはユーザーとして無いのでこれも無視できる
    • プロフィールから "ファイル付きノート"　はある

その他

問題なさそうならついでに行いたいもの

noteテーブルとpackages/backend/src/models/Note.ts　

以下のインデックスに対応するカラムについてGINインデックスがあるが btree インデックスが残っている

• IDX_NOTE_VISIBLE_USER_IDS
• IDX_NOTE_MENTIONS
• IDX_NOTE_TAGS

対応

• 明示的なbtreeインデックス削除
• 複合インデックスでないのでcolumnの上にアノテーションする
  • 特にルールは無いと思いますがこの方が見やすいと思っただけです

🥰 Expected Behavior

結果が返って表示される

🤬 Actual Behavior

時間がかかるのでノート数が多いサーバーでDBクエリタイムアウトを設定している場合に返ってこない

📝 Steps to Reproduce

1. ドライブで任意のファイルを選択する
2. 添付されているノートを開く

💻 Frontend Environment

* Model and OS of the device(s):
* Browser:
* Server URL:
* Misskey:

🛰 Backend Environment (for server admin)

* Installation Method or Hosting Service: development environment
* Misskey: 2023.12.2
* Node: 20.10
* PostgreSQL: 15.1
* Redis: 7.0.8
* OS and Architecture: 22.04.3 LTS amd64

Do you want to address this bug yourself?

• Yes, I will patch the bug myself and send a pull request

[u1-liquid]

FYI: MisskeyIO#239

[yszkst]

@u1-liquid 後で取り込むということならこのIssueをCloseします。

[yszkst]

インデックス追加と MisskeyIO@24d2f2a を cherry-pick してPRにしてみます