Fediver

CREATE OR REPLACE FUNCTION public.timestamp_to_aid(t timestamptz) RETURNS text AS $$
DECLARE
    time_part text;
    num bigint;
BEGIN
	num := extract(epoch from t) * 1000 + mod(extract(millisecond from t),1000)::integer - 946684800000;
    time_part := '00';
    FOR i IN 1..8 LOOP
		time_part := substring('0123456789abcdefghijklmnopqrstuvwxyz', (1 + mod(num, 36))::integer, 1) || time_part;
		num := num / 36;
    END LOOP;
    RETURN time_part;
END;
$$ LANGUAGE plpgsql;

select n.* from "note" n
where
	not exists (select 1 from note reply where reply."replyId" = n.id)
	and not exists (select 1 from note renote where renote."renoteId" = n.id)
	and not exists (select 1 from note_favorite nf where nf."noteId" = n.id )
	and n."clippedCount" =0
	and id < timestamp_to_aid(current_date - 56)
order by id desc;

hmm
なんかweb側からこんなログが出てたぞ…

query is slow: SELECT "ChartXinstance"."id" AS "ChartXinstance_id", "ChartXinstance"."date" AS "ChartXinstance_date", "ChartXinstance"."group" AS "ChartXinstance_group", "ChartXinstance"."___requests_failed" AS "ChartXinstance____requests_failed", "ChartXinstance"."___requests_succeeded" AS "ChartXinstance____requests_succeeded", "ChartXinstance"."___requests_received" AS "ChartXinstance____requests_received", "ChartXinstance"."___notes_total" AS "ChartXinstance____notes_total", "ChartXinstance"."___notes_inc" AS "ChartXinstance____notes_inc", "ChartXinstance"."___notes_dec" AS "ChartXinstance____notes_dec", "ChartXinstance"."___notes_diffs_normal" AS "ChartXinstance____notes_diffs_normal", "ChartXinstance"."___notes_diffs_reply" AS "ChartXinstance____notes_diffs_reply", "ChartXinstance"."___notes_diffs_renote" AS "ChartXinstance____notes_diffs_renote", "ChartXinstance"."___notes_diffs_withFile" AS "ChartXinstance____notes_diffs_withFile", "ChartXinstance"."___users_total" AS "ChartXinstance____users_total", "ChartXinstance"."___users_inc" AS "ChartXinstance____users_inc", "ChartXinstance"."___users_dec" AS "ChartXinstance____users_dec", "ChartXinstance"."___following_total" AS "ChartXinstance____following_total", "ChartXinstance"."___following_inc" AS "ChartXinstance____following_inc", "ChartXinstance"."___following_dec" AS "ChartXinstance____following_dec", "ChartXinstance"."___followers_total" AS "ChartXinstance____followers_total", "ChartXinstance"."___followers_inc" AS "ChartXinstance____followers_inc", "ChartXinstance"."___followers_dec" AS "ChartXinstance____followers_dec", "ChartXinstance"."___drive_totalFiles" AS "ChartXinstance____drive_totalFiles", "ChartXinstance"."___drive_incFiles" AS "ChartXinstance____drive_incFiles", "ChartXinstance"."___drive_decFiles" AS "ChartXinstance____drive_decFiles", "ChartXinstance"."___drive_incUsage" AS "ChartXinstance____drive_incUsage", "ChartXinstance"."___drive_decUsage" AS "ChartXinstance____drive_decUsage" FROM "__chart__instance" "ChartXinstance" WHERE (("ChartXinstance"."date" = $1) AND ("ChartXinstance"."group" = $2)) LIMIT 1