Misskeyで久しぶりに投稿したフォロー中のアカウントを一覧にする

select c.group, d.m from (select "group" from following left join __chart__per_user_notes on "group" = "followeeId" where "followerId"='?' group by "group" having max("date") > ?) as c left join (select "group", max("date") as m from __chart__per_user_notes where "date" < ? group by "group") as d on c.group = d.group order by m asc;

SQLのキーワードを大文字で書くか

チャートはパフォーマンス的に面倒なので、複合インデックス(userId, id)を張ってこうした方がいい

SELECT c."userId", d.m
FROM (
    SELECT DISTINCT ON (f."followeeId") f."followeeId" AS "userId"
    FROM "following" f
    JOIN "note" n ON n."userId" = f."followeeId"
    WHERE f."followerId" = '9g2o6eycu3'
      AND n."id" > '9zh5gxyzpa'
    ORDER BY f."followeeId", n."id" DESC
) AS c
LEFT JOIN LATERAL (
    SELECT "id" AS m
    FROM "note"
    WHERE "userId" = c."userId" AND "id" < '9zh5gxyzpa'
    ORDER BY "id" DESC
    LIMIT 1
) AS d ON true
ORDER BY d.m ASC;

おはようございます、おひさまみなさま

実装できた

RE: https://misskey-square.net/notes/9zww88v4ll

Postgresqlくんなんでや…

explain SELECT c."userId" as user, d.m as last FROM ( SELECT DISTINCT ON (f."followeeId") f."followeeId" AS "userId" FROM "following" f JOIN "note" n ON n."userId" = f."followeeId" WHERE f."followerId" = '9g2o6eycu3' AND n."id" > '9zxmywxg1y' AND n."visibility" <> 'specified' AND COALESCE(n."renoteId", 'x') = 'x' AND COALESCE(n."replyId", 'x') = 'x' ORDER BY f."followeeId", n."id" DESC) AS c LEFT JOIN LATERAL ( SELECT "id" AS m FROM "note" WHERE "userId" = c."userId" AND "id" <= '9zxmywxg1y' AND note."visibility" <> 'specified' AND COALESCE(note."renoteId", 'x') = 'x' ORDER BY "id" DESC LIMIT 1) AS d ON true ORDER BY d.m ASC NULLS FIRST OFFSET 0 LIMIT 10;

=>

->  Index Scan Backward using idx_per_user_notes on note  (cost=0.56..22146.90 rows=28 width=11)
        Index Cond: ((("userId")::text = (f."followeeId")::text) AND ((id)::text <= '9zxmywxg1y'::text))

explain SELECT c."userId" as user, d.m as last FROM ( SELECT DISTINCT ON (f."followeeId") f."followeeId" AS "userId" FROM "following" f JOIN "note" n ON n."userId" = f."followeeId" WHERE f."followerId" = '9g2o6eycu3' AND n."id" > '9zxmywxg1y' AND n."visibility" <> 'specified' AND COALESCE(n."renoteId", 'x') = 'x' AND COALESCE(n."replyId", 'x') = 'x' ORDER BY f."followeeId", n."id" DESC) AS c LEFT JOIN LATERAL ( SELECT "id" AS m FROM "note" WHERE "userId" = c."userId" AND "id" <= '9zxmywxg1y' AND note."visibility" <> 'specified' AND COALESCE(note."renoteId", 'x') = 'x' AND COALESCE(note."replyId", 'y')  = 'y' ORDER BY "id" DESC LIMIT 1) AS d ON true ORDER BY d.m ASC NULLS FIRST OFFSET 0 LIMIT 10;

=>

->  Index Scan using "IDX_5b87d9d19127bd5d92026017a7" on note  (cost=0.56..21357.10 rows=1 width=11)
     Index Cond: (("userId")::text = (f."followeeId")::text)

変なハックをやめたら逆にうまくいった

フォロー中ユーザーの最近の投稿を表示する機能が本サーバーに導入されました。画面上部の人アイコンをタップすると表示できます。久しぶりに投稿したアカウントを優先して表示する仕様になっています。

新機能の画面で、フォロワー限定投稿が（非公開）と表示される不具合が生じていますが、時間をタップすると普通に読めます。
そのうち直します。

この機能作ってよかったと今自分で使いながら思っている

@astro_ray Yeah, it's hard to be aware of what you have missed.

とりあえずご飯食べよー

直近3日を表示するタブに「直近4日」という名前をつけるアホがここにいます
どうして

YouTubeの共有ボタンからリンクをコピーするとご丁寧にトラッキングIDがついてくるので、投稿とか送信するときはいつも削っている
なんか相手に失礼な気がして…

たまには放置しまくりのポッドキャストでも聴くか