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·1 year agoFor post listing, these are the three that aren’t simple to grasp: SortType::Active => query .then_order_by(post_aggregates::hot_rank_active.desc()) .then_order_by(post_aggregates::published.desc()), SortType::Hot => query .then_order_by(post_aggregates::hot_rank.desc()) .then_order_by(post_aggregates::published.desc()), SortType::Controversial => query.then_order_by(post_aggregates::controversy_rank.desc()),
minus-squareRoundSparrow @ BT@bulletintree.comOPMlinkfedilinkarrow-up1·edit-21 year ago hot_rank: 1728, hot_rank_active: 1728, -- Note: 1728 is the result of the hot_rank function, with a score of 1, posted now -- hot_rank = 10000*log10(1 + 3)/Power(2, 1.8)
minus-squareRoundSparrow @ BT@bulletintree.comOPMlinkfedilinkarrow-up1·1 year agoDifference between hot_rank and hot_rank_active SET hot_rank = hot_rank(a.score, a.published), hot_rank_active = hot_rank(a.score, a.newest_comment_time_necro)"
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; $$;
For post listing, these are the three that aren’t simple to grasp:
SortType::Active => query .then_order_by(post_aggregates::hot_rank_active.desc()) .then_order_by(post_aggregates::published.desc()), SortType::Hot => query .then_order_by(post_aggregates::hot_rank.desc()) .then_order_by(post_aggregates::published.desc()), SortType::Controversial => query.then_order_by(post_aggregates::controversy_rank.desc()),
Difference between hot_rank and hot_rank_active
SET hot_rank = hot_rank(a.score, a.published), hot_rank_active = hot_rank(a.score, a.newest_comment_time_necro)"
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; $$;