RoundSparrow @ BT@bulletintree.comM to Lemmy Project Priorities Observations@bulletintree.com · 1 year ago2023-08-18message-squaremessage-square13fedilinkarrow-up11arrow-down10
arrow-up11arrow-down1message-square2023-08-18RoundSparrow @ BT@bulletintree.comM to Lemmy Project Priorities Observations@bulletintree.com · 1 year agomessage-square13fedilink
minus-squareRoundSparrow @ BT@bulletintree.comOPMlinkfedilinkarrow-up1·edit-21 year agolemmy@lemmy_alpha LOG: duration: 50.220 ms execute : WITH batch AS (SELECT a.id FROM post_aggregates a WHERE a.published > $1 AND (a.hot_rank != 0 OR a.hot_rank_active != 0) ORDER BY a.published LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE post_aggregates a SET hot_rank = hot_rank(a.score, a.published), hot_rank_active = hot_rank(a.score, a.newest_comment_time_necro) FROM batch WHERE a.id = batch.id RETURNING a.published; 2023-08-18 09:00:34.578 MST [1877420] lemmy@lemmy_alpha DETAIL: parameters: $1 = '2023-08-16 23:40:31.149267', $2 = '1000'
minus-squareRoundSparrow @ BT@bulletintree.comOPMlinkfedilinkarrow-up1·1 year agoCREATE FUNCTION public.hot_rank(score numeric, published timestamp without time zone) RETURNS integer LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS $$ DECLARE hours_diff numeric := EXTRACT(EPOCH FROM (timezone('utc', now()) - published)) / 3600; BEGIN IF (hours_diff > 0) THEN RETURN floor(10000 * log(greatest (1, score + 3)) / power((hours_diff + 2), 1.8))::integer; ELSE RETURN 0; END IF; END; $$;
lemmy@lemmy_alpha LOG: duration: 50.220 ms execute : WITH batch AS (SELECT a.id FROM post_aggregates a WHERE a.published > $1 AND (a.hot_rank != 0 OR a.hot_rank_active != 0) ORDER BY a.published LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE post_aggregates a SET hot_rank = hot_rank(a.score, a.published), hot_rank_active = hot_rank(a.score, a.newest_comment_time_necro) FROM batch WHERE a.id = batch.id RETURNING a.published; 2023-08-18 09:00:34.578 MST [1877420] lemmy@lemmy_alpha DETAIL: parameters: $1 = '2023-08-16 23:40:31.149267', $2 = '1000'
CREATE FUNCTION public.hot_rank(score numeric, published timestamp without time zone) RETURNS integer LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS $$ DECLARE hours_diff numeric := EXTRACT(EPOCH FROM (timezone('utc', now()) - published)) / 3600; BEGIN IF (hours_diff > 0) THEN RETURN floor(10000 * log(greatest (1, score + 3)) / power((hours_diff + 2), 1.8))::integer; ELSE RETURN 0; END IF; END; $$;