emoji-award-marseywholesome
Unable to load image
/h/slackernews Emoji Award given by @HeyMoon: "marseywholesome" House Furry
@Aevann's profile picture @Aevann's hat
Aevann capy/bara world's largest rodent  1yr ago (text post) Edited 1yr ago 1,805 thread views #143031
Reported by:

need help with "simps" and "simps for"

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)

115
Hot Old Top Bottom Controversial Random
Jump in the discussion.

No email address required.

View entire discussion

Really should not take that long for the amount of data shown in the query plan. A quick look at the schema suggests you need more indexes, composite indexes, to cover this query. It's relying on the comments.author_id index, and looping for the rest.

Fact table would work as people suggest, but a lot of overhead to achieve something that could be fixed with better DB architecture.

Jump in the discussion.

No email address required.

what indexes would u make

Jump in the discussion.

No email address required.

I'll take a stab at it later today. Been a few years since I've used Postgres.

Jump in the discussion.

No email address required.

My test data is not very valid, hard to tell without actual data, but inserting hundreds of thousands of comments and about 70 votes each from random users.

-- See if this new index alone fixes issue


CREATE INDEX commentvotes_commentid_userid_votetype_idx ON public.commentvotes USING btree(comment_id ASC, user_id ASC, vote_type ASC)


-- Alternative way to write query, but didn't see a difference in query plan with my sample data


SELECT
    cv.user_id
    ,count(cv.comment_id) votes
FROM public.commentvotes cv
WHERE 
    cv.vote_type = 1
AND cv.comment_id IN (
    SELECT id 
    FROM public.comments
    WHERE author_id = 1
        AND deleted_utc = 0
        AND is_banned = false
        AND ghost = false
)
GROUP BY cv.user_id
ORDER BY count(cv.comment_id) DESC
Jump in the discussion.

No email address required.

CREATE INDEX commentvotes_commentid_userid_votetype_idx ON public.commentvotes USING btree(comment_id ASC, user_id ASC, vote_type ASC)

i really idk what i'm talking about,

but this doesn't create a btree entry for every permutation of (comment_id, user_id, and vote_type)?

Jump in the discussion.

No email address required.

This is the right answer. If any query is too slow the answer is always to add indexes until you don't see any more table scans

Jump in the discussion.

No email address required.

Top Poster of the Day:
sneedman
Current Registered Users: 25,766
sidebar image

tech/science swag. :marscientist:

Effortposts made by SN Chads

Guidelines:

What to Submit

On-Topic: Anything that good slackers would find interesting. That includes more than /g/ memes and slacking off. If you had to reduce it to a sentence, the answer might be: anything that gratifies one's intellectual laziness.

Off-Topic: Most stories about politics, or crime, or sports, unless they're evidence of some interesting new phenomenon. Videos of pratfalls or disasters, or cute animal pictures. If they'd cover it on TV news, it's probably lame.

Help keep this hole healthy by keeping drama and non-drama balanced. If you see too much drama, post something that isn't dramatic. If there isn't enough drama and this hole has become too boring, POST DRAMA!

In Submissions

Please do things to make titles stand out, like using uppercase or exclamation points, or saying how great an article is. It should be explicit in submitting something that you think it's important.

Please don't submit the original source. If the article is behind a paywall, just post the text. If a video is behind a paywall, post a magnet link. Fuck journos.

Please don't ruin the hole with chudposts. It isn't funny and doesn't belong here. THEY WILL BE MOVED TO /H/CHUDRAMA

If the title includes the name of the site, please leave that in, because our users are too stupid to know the difference between a url and a search query.

If you submit a video or pdf, please don't warn us by appending [video] or [pdf] to the title. That would be r-slurred. We're not using text-based browsers. We know what videos and pdfs are.

Make sure the title contains a gratuitous number or number + adjective. Good clickbait titles are like "Top 10 Ways to do X" or "Don't do these 4 things if you want X"

Otherwise editorialize. Please don't use the original title, unless it is gay or r-slurred, or you're shits all fucked up.

If you're going to post old news (at least 1 year old), please flair it so we can mock you for living under a rock, or don't and we'll mock you anyway.

Please don't post on SN to ask or tell us something. Send it to [email protected] instead.

If your post doesn't get enough traction, try to delete and repost it.

Please don't use SN primarily for promotion. It's ok to post your own stuff occasionally, but the primary use of the site should be for curiosity. If you want to astroturf or advertise, post on news.ycombinator.com instead.

Please solicit upvotes, comments, and submissions. Users are stupid and need to reminded to vote and interact. Thanks for the gold, kind stranger, upvotes to the left.

In Comments

Be snarky. Don't be kind. Have fun banter; don't be a dork. Please don't use big words like "fulminate". Please sneed at the rest of the community.

Comments should get more enlightened and centrist, not less, as a topic gets more divisive.

If disagreeing, please reply to the argument and call them names. "1 + 1 is 2, not 3" can be improved to "1 + 1 is 3, not 2, mathfaggot"

Please respond to the weakest plausible strawman of what someone says, not a stronger one that's harder to make fun of. Assume that they are bad faith actors.

Eschew jailbait. Paedophiles will be thrown in a wood chipper, as pertained by sitewide rules.

Please post shallow dismissals, especially of other people's work. All press is good press.

Please use Slacker News for political or ideological battle. It tramples weak ideologies.

Please comment on whether someone read an article. If you don't read the article, you are a cute twink.

Please pick the most provocative thing in an article or post to complain about in the thread. Don't nitpick stupid crap.

Please don't be an unfunny chud. Nobody cares about your opinion of X Unrelated Topic in Y Unrelated Thread. If you're the type of loser that belongs on /h/chudrama, we may exile you.

Sockpuppet accounts are encouraged, but please don't farm dramakarma.

Please use uppercase for emphasis.

Please post deranged conspiracy theories about astroturfing, shilling, bots, brigading, foreign agents and the like. It degrades discussion and is usually mistaken. If you're worried about abuse, email [email protected] and dang will add you to their spam list.

Please don't complain that a submission is inappropriate. If a story is spam or off-topic, report it and our moderators will probably do nothing about it. Feed egregious comments by replying instead of flagging them like a pussy. Remember: If you flag, you're a cute twink.

Please don't complain about tangential annoyances—things like article or website formats, name collisions, or back-button breakage. That's too boring, even for HN users.

Please seethe about how your posts don't get enough upvotes.

Please don't post comments saying that rdrama is turning into ruqqus. It's a nazi dogwhistle, as old as the hills.

Miscellaneous:

We reserve the right to exile you for whatever reason we want, even for no reason at all! We also reserve the right to change the guidelines at any time, so be sure to real them at least once a month. We also reserve the right to ignore enforcement of the guidelines at the discretion of the janitorial staff. Be funny, or at least compelling, and pretty much anything legal is welcome provided it's on-topic, and even then.

Do not use outdated operating systems that are unsupported to access SN. What are you, poor?

[[[ To any NSA and FBI agents reading my email: please consider ]]]

[[[ whether defending the US Constitution against all enemies, ]]]

[[[ foreign or domestic, requires you to follow Snowden's example. ]]]

https://i.rdrama.net/images/1663436125937968.webp

BROWSE EFFORTPOSTS SITE GUIDE DIRECTORY Emojis & Art | Info Megathreads HOLES PING GROUPS
/h/slackernews LOG /h/slackernews MODS /h/slackernews EXILEES /h/slackernews FOLLOWERS /h/slackernews BLOCKERS
Live commit: b2ec9d6
Link copied to clipboard
Action successful!
Error, please refresh the page and try again.