need help with "simps" and "simps for"

Have you tried with making them stored procs or pre compiled views?

In awe at the level of 'do it for free' that's been attained here

Just code better?

console.log('Make faster');

My man. My neighbor. Stick them both in temp tables so you're not fricking around with the entire dataset before your where clause.

reorder columns to retvrn the 13.52% wasted padding bytes (if you skimped out on ram)

    CREATE TABLE public.commentvotes (
        comment_id integer NOT NULL,
        user_id integer NOT NULL,
        created_utc integer NOT NULL,
        app_id smallint,
        coins smallint DEFAULT 1 NOT NULL,
        vote_type char NOT NULL,
        "real" boolean DEFAULT true NOT NULL
    );

u trying my index suggestions?

Replicate all the votes data into a graph database (you'll have to install anoter DBMS) and run the query on it. Then you would have to query for the number of all incoming edges userA-[:vote]->userB

my friend here says he doesnt know what the simps thing means

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.

Sorting 2293 rows takes almost 14 seconds? Neighbor wat??? A cpu from the 90s could do better than that.

EDIT: Oh wait im looking at the query plan wrong. Bruh have you ever considered sharding?

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):

Simps²: n% of OTHERUSER's total upvotes are for my comments.

Haters²: n% of OTHERUSER's total downvotes are for my comments.

Simps for²: I upvote n% of OTHERUSER's total comments.

Hates²: I downvote n% of OTHERUSER's total comments.

Needs more racial slurs

SELECT neighbor FROM straggot;

How often are you querying it? Can't you just compute it when the Indians/Euros are active and store the value daily or something

?

im too stupid to help :3

!slots100

!slots100

Learn to code

Reject , embrace

Make viewing numbers of simps free, like how it used to be

Make viewing the specific comments and posts that were simped for require all seeing eye or a new a profile upgrade

Also bring back old search, I would unironically pay dramacoin per search to use old search again the new one is very unneedful

It is obvious that the code class runs into a server error when trying to parse the matrix operations applied to global variables. I would suggest that you implement a new library script that would unlock and boot the mainframe before docking the executed commands to the automation subroutines. Additionally you could disable the back-end index inhibitor arrays and reroute the computational power to overclock the server CPU ticks

Child's play really

Switch to rust

>using sql in 2023

NoSQL bros ww@

you could try change the index scan to index-only scan (no access to table) by adding indexes like (vote_type, comment_id) (vote_type, user_id). or try the other way around, (comment_id, vote_type) and (user_id, vote_type), then you could probably delete the existing superfluous user_id index for commentvotes. i'm curious how much that would change

We actually discussed it on groomercord a couple of days ago, looks like we can't really speed up counting so we need a table for totals and update it using a trigger (a rare time when it's justified).

Think other users are right and it's time for a non-live fact table. You could probably put some indexes to speed things up, but the ultimate cost of that is going to be slower and slower writes to the comments and comment votes tables.

You could populate an aggregated fact table during quiet time with:

User_id, user_id_fk, upvote_count, downvote_count refresh it weekly or daily whatever, and run the queries against that.

I agree with caching the calculated value like others have said.

Another thing you could do is denormalize by including the comment author ID in commentvotes. That would eliminate the join and allow you to index commentvotes by (user_id, author_id) and (author_id, user_id).

(cost=0.44..17.28 rows=32 width=8) (actual time=1.447..5.654 rows=6 loops=13141)

postgres assumes you get 32 upvotes on average but it's actually 6. you've got to make better comments

This doesn't need to be perfectly accurate or live. It's a good use case for some kind of key-value lookup cache or a daily/weekly batch query that calculates it for each user and then stores the result.

Run a cron job once or twice a day to set values for every user.

Employ some indians to count manually

Generate random users and vote counts if the query takes more than a couple seconds - no one can verify the counts without DB access so they can't deboonk it

Im barely a codecel and not going to read all that shit, by why isn't simping handled as part of upvoting/downvoting stuff?

Instead of running it every time a user clicks run it once per day and cache the result

Instead of checking every vote the user has made every time the function is called, store the result of the previous call and only check the votes which have been made since then.

bout to sleep so didn't read but I'm sure this can be solved by hashing

You could aggregate the comment votes into a summary table and run that as a scheduled task every x hours. Or have insert / updates also update the summary table.

Not a codecel, but would limiting the scope to the last X comments or Y time improve it? Might be adding complexity but if the query only checked comments made since the last time that it checked, it would probably solve the power user problem.

Good duck for good luck!

Index the table for power users. Update it every 2 hours or so.

lmao i was using the simps for panel a view days ago to check out if i can make the server go unresponsive

easiest solution is to save the result and dont update them more than once a week.

make a separate commentvotes table for power users like @chiobu

ANALYZE commentvotes ?

The loop counts do not look healthy whatsoever.

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

Ban powerusers

@FormerLurker WONDER ABOUT THIS BECAUSE @FormerLurker LIKE TO FREQUENTLY REWARD SIMPS WITH DRAMACOIN. @FormerLurker GLAD TO BE ABLE TO DO THIS AGAIN.

@FormerLurker ALSO IMPRESSED TO LEARN EGYPTIANS SHOWER. @FormerLurker WAS UNDER IMPRESSION EGYPTOIDS TAKE SAND BATHS OR WASH IN NILE.

TRANS LIVES MATTER

so the answer might be pretty obvious (correct answer gets 50k marseybuckerinos)

You need to trаnsition

HRT and programmer thigh highs will deffo solve this