• RoundSparrow @ BT@bulletintree.comOPM
    1·
    1 year ago
    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'
    • RoundSparrow @ BT@bulletintree.comOPM
      1·
      1 year ago
      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;
$$;