so yesterday I restored "simps" and "simps for" but made them only count post votes
the reason is because the queries that would include comment votes take way too long when they run on powerusers which causes timeouts that slow down the whole site
here's 2 examples that use my user id
QUERY 1 (for my simps): 25 seconds
explain analyze SELECT commentvotes.user_id AS commentvotes_user_id, count(commentvotes.user_id) AS count_1
FROM commentvotes JOIN comments ON comments.id = commentvotes.comment_id
WHERE comments.ghost = false AND comments.is_banned = false AND comments.deleted_utc = 0 AND commentvotes.vote_type = 1 AND comments.author_id = 1 GROUP BY commentvotes.user_id ORDER BY count(commentvotes.user_id) DESC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=101326.43..101331.71 rows=2111 width=12) (actual time=25292.671..25371.754 rows=1844 loops=1)
Sort Key: (count(commentvotes.user_id)) DESC
Sort Method: quicksort Memory: 135kB
-> Finalize GroupAggregate (cost=100675.05..101209.87 rows=2111 width=12) (actual time=25266.703..25370.338 rows=1844 loops=1)
Group Key: commentvotes.user_id
-> Gather Merge (cost=100675.05..101167.65 rows=4222 width=12) (actual time=25266.210..25368.234 rows=4089 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=99675.02..99680.30 rows=2111 width=12) (actual time=25174.301..25174.732 rows=1363 loops=3)
Sort Key: commentvotes.user_id
Sort Method: quicksort Memory: 113kB
Worker 0: Sort Method: quicksort Memory: 113kB
Worker 1: Sort Method: quicksort Memory: 111kB
-> Partial HashAggregate (cost=99537.35..99558.46 rows=2111 width=12) (actual time=25172.482..25173.130 rows=1363 loops=3)
Group Key: commentvotes.user_id
Batches: 1 Memory Usage: 241kB
Worker 0: Batches: 1 Memory Usage: 241kB
Worker 1: Batches: 1 Memory Usage: 241kB
-> Nested Loop (cost=119.48..99291.83 rows=49104 width=4) (actual time=70.552..25064.475 rows=25093 loops=3)
-> Parallel Bitmap Heap Scan on comments (cost=119.04..12311.25 rows=4943 width=4) (actual time=57.412..246.591 rows=4380 loops=3)
Recheck Cond: (author_id = 1)
Filter: ((NOT ghost) AND (NOT is_banned) AND (deleted_utc = 0))
Rows Removed by Filter: 263
Heap Blocks: exact=3882
-> Bitmap Index Scan on comments_user_index (cost=0.00..116.08 rows=12353 width=0) (actual time=36.276..36.277 rows=14518 loops=1)
Index Cond: (author_id = 1)
-> Index Scan using commentvotes_pkey on commentvotes (cost=0.44..17.28 rows=32 width=8) (actual time=1.447..5.654 rows=6 loops=13141)
Index Cond: (comment_id = comments.id)
Filter: (vote_type = 1)
Rows Removed by Filter: 0
Planning Time: 0.764 ms
JIT:
Functions: 60
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 19.575 ms, Inlining 0.000 ms, Optimization 6.942 ms, Emission 122.562 ms, Total 149.079 ms
Execution Time: 25453.921 ms
(36 rows)
QUERY 2 (for the ppl i simp for): 13 seconds
explain analyze SELECT comments.author_id AS comments_author_id, count(comments.author_id) AS count_1
FROM comments JOIN commentvotes ON comments.id = commentvotes.comment_id
WHERE comments.ghost = false AND comments.is_banned = false AND comments.deleted_utc = 0 AND commentvotes.vote_type = 1 AND commentvotes.user_id = 1 GROUP BY comments.author_id ORDER BY count(comments.author_id) DESC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=234059.08..234064.81 rows=2293 width=12) (actual time=13511.566..13632.448 rows=3074 loops=1)
Sort Key: (count(comments.author_id)) DESC
Sort Method: quicksort Memory: 241kB
-> Finalize GroupAggregate (cost=233350.16..233931.10 rows=2293 width=12) (actual time=13502.703..13630.725 rows=3074 loops=1)
Group Key: comments.author_id
-> Gather Merge (cost=233350.16..233885.24 rows=4586 width=12) (actual time=13502.611..13628.135 rows=6597 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=232350.14..232355.87 rows=2293 width=12) (actual time=13404.805..13405.287 rows=2199 loops=3)
Sort Key: comments.author_id
Sort Method: quicksort Memory: 203kB
Worker 0: Sort Method: quicksort Memory: 199kB
Worker 1: Sort Method: quicksort Memory: 197kB
-> Partial HashAggregate (cost=232199.23..232222.16 rows=2293 width=12) (actual time=13398.724..13399.556 rows=2199 loops=3)
Group Key: comments.author_id
Batches: 1 Memory Usage: 369kB
Worker 0: Batches: 1 Memory Usage: 369kB
Worker 1: Batches: 1 Memory Usage: 369kB
-> Nested Loop (cost=0.87..231821.34 rows=75577 width=4) (actual time=81.541..13275.534 rows=52615 loops=3)
-> Parallel Index Scan using cvote_user_index on commentvotes (cost=0.44..129890.24 rows=78687 width=4) (actual time=64.216..9154.821 rows=55000 loops=3)
Index Cond: (user_id = 1)
Filter: (vote_type = 1)
Rows Removed by Filter: 90
-> Index Scan using comments_pkey on comments (cost=0.43..1.30 rows=1 width=8) (actual time=0.072..0.072 rows=1 loops=165000)
Index Cond: (id = commentvotes.comment_id)
Filter: ((NOT ghost) AND (NOT is_banned) AND (deleted_utc = 0))
Rows Removed by Filter: 0
Planning Time: 0.721 ms
JIT:
Functions: 60
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 8.837 ms, Inlining 0.000 ms, Optimization 11.450 ms, Emission 167.933 ms, Total 188.220 ms
Execution Time: 13635.714 ms
(33 rows)
so i need help, how do I make them run faster or what indexes do I make, i didnt actually read the explain analyze outputs I posted here cuz im too lazy and need to take a shower rn so the answer might be pretty obvious (correct answer gets 50k marseybuckerinos and a code contributer badge)
Jump in the discussion.
No email address required.
Can't help you with the code, apart from to say maybe make the calculations less frequent rather than in real time?
I can however reintroduce the latest rendition of my "common sense" proportional system which Snakes was considering before he resigned as janny!
It would be interesting to see the data as a proportion of total votes made by the person giving the votes. For example if I've upvoted someone (say LLM) a thousand times, that sounds like a big number, but if I've given away a million upvotes to all users in total, then 1000 upmarseys is barely anything. I'm hardly a simp. On the other hand, if I've given LLM a hundred upvotes, but have only given out 120 upvotes to everyone in total, that's very significant, and makes me a giant simp for LLM. The "haters" section is similar but with downvotes of course. That would really spice things up as you might imagine.
That's "Simps" and "Haters" covered. As for the "Simps for", and "Hates" section, well, you're apparently the third biggest user I simp for. But you do post more than most people, so perhaps your comments have caught my eye and made me upvote you more simply because of that, so we should divide the score by the total number of comments (instead of votes like the previous paragraph) you made for a fairer comparison.
To recap and to simplify things even further, the maths is as simple as just saying (with my profile as an example):
Simps2:
n% of OTHERUSER's total upvotes are for my comments.Haters2:
n% of OTHERUSER's total downvotes are for my comments.Simps for2:
I upvote n% of OTHERUSER's total comments.Hates2:
I downvote n% of OTHERUSER's total comments.Jump in the discussion.
No email address required.
Have you owned the libs yet?
Jump in the discussion.
No email address required.
Ages ago.
Jump in the discussion.
No email address required.
More options
Context
More options
Context
Jump in the discussion.
No email address required.
More options
Context
More options
Context