ノート数が多いインスタンスで、フォローが少ないとホームタイムラインの読み込みでクエリタイムアウトになる

[syuilo]

💡 Summary

🥰 Expected Behavior

🤬 Actual Behavior

📝 Steps to Reproduce

📌 Environment

Misskey version:
Your OS:
Your browser:

[syuilo]

インデックス張ってあるのに何故だろう

[tamaina]

リストやローカルもめっちゃ遅い感じが？

[tamaina]

日付順にソートするのに時間かかってるとか（適当）

[acid-chicken]

• フォローが少ない（厳密にはフォローしているユーザーの書き込みが少ない）ユーザーのホーム
• リモートの投稿割合がかなり高いサーバー（所謂お一人様など）のローカル

みたいな、最新から数えていってタイムラインに表示すべき投稿を埋めるまでかなりの数をシークする必要があるケースで遅くなっている気がする（つまりシーケンシャルスキャンしている→インデックスが効いてない）

[acid-chicken]

ミュートなりブロックなりでインデックスが効かなくなる説

[syuilo]

例えば一ヶ月以内の投稿しか対象にしないようにしたら改善するかな

[syuilo]

例えば一ヶ月以内の投稿しか対象にしないようにしたら改善するかな

効果なかった

[tamaina]

MastodonとかはRedisにタイムラインを保持している（DBの検索はやらないとかなんとか）

[syuilo]

Issueはこちら
#9325

[tamaina]

あるんだ

[tamaina]

でもシークが問題なら、あまり参照されない古い投稿を取り出すのも遅いんじゃね？

[tamaina]

いやノートを検査しまくらないから早くなるか

[tamaina]

ユーザーのファイル付き投稿一覧を取得するのもしんどかったりする

[xianonn]

misskey/packages/backend/src/server/api/endpoints/notes/timeline.ts

Lines 74 to 80 in 5af8b77

	const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'),
	ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate)
	.andWhere('note.createdAt > :minDate', { minDate: new Date(Date.now() - (1000 * 60 * 60 * 24 * 30)) }) // 30日前まで
	.andWhere(new Brackets(qb => { qb
	.where('note.userId = :meId', { meId: me.id });
	if (hasFollowing) qb.orWhere(`note.userId IN (${ followingQuery.getQuery() })`);
	}))

L78 と L79 が OR で繋がっているのでインデックスが使えていないのだと思います。

この部分を SQL で書くと

WHERE
  "note"."createdAt" > $1 AND
  (
    "note"."userId" = $2 OR
    "note"."userId" IN (SELECT
        "following"."followeeId" AS "following_followeeId"
      FROM
        "following" "following"
      WHERE
        "following"."followerId" = $3)
  )

のようになりますが、これを OR を使わない形に変える、例えば UNION を使って

WHERE
  "note"."createdAt" > $1 AND
  (
    "note"."userId" IN (SELECT
        "following"."followeeId" AS "following_followeeId"
      FROM
        "following" "following"
      WHERE
        "following"."followerId" = $3
      UNION ALL
      SELECT $2)
  )

とすると速くなります。

ですがまだ数秒かかってしまう感じ(ちゃんと調べてませんが、多分サブクエリが複数回呼ばれてその分遅くなっている？)なので、この部分を内部結合にして

FROM
  "note" "note"
  INNER JOIN (SELECT
        "following"."followeeId" "id"
      FROM
        "following" "following"
      WHERE
        "following"."followerId" = $3
      UNION ALL
      SELECT $2) "followeeOrMe" ON
    "followeeOrMe"."id"="note"."userId"

(略)

WHERE
  "note"."createdAt" > $1

とすると一瞬で返るようになります(内部結合にしたことで1回だけ呼ばれるようになるはず)。

ただ TypeORM では UNION がサポートされていないっぽい。

[Ryokusa]

あまり詳しくなくて恐縮なのですが、フォローがあるユーザーはIN句で自身もまとめて検索するのはどうでしょうか？
L77~L79の範囲

if (hasFollowing){
    //IN句で自身ID、フォローIDからノート取得
}else{
    //自身IDからノート取得
}

冗長かもしれませんがORでインデックスが外れることを考えるとこっちでも良いのではと思いました
Brackets の書き方がわからず申し訳ない…

[xianonn]

フォローがあるユーザーはIN句で自身もまとめて検索、のところは上で書いたやつの 1 つ目の案になりますね。

UNION は TypeORM で対応しておらずクエリ直書きの力技になってしまうので他の方法を考えているところ。
2 つ目の案の followeeOrMe の部分を VIEW で作ってしまえば普通のテーブルのように扱える(TypeORM が VIEW に対応していれば。未確認)が、これだけのために VIEW を作るのもなんだか。

1 クエリになっているのを崩さないように考えていたが、先にフォロイーを取得してしまって 2 クエリにすれば解決しそうなのでやってみる。