INSERT INTO "comment_like" ("person_id", "comment_id", "post_id", "score") VALUES ($1, $2, $3, $4) ON CONFLICT ("comment_id", "person_id") DO UPDATE SET "person_id" = $5, "comment_id" = $6, "post_id" = $7, "score" = $8 RETURNING "comment_like"."id", "comment_like"."person_id", "comment_like"."comment_id", "comment_like"."post_id", "comment_like"."score", "comment_like"."published"
~~The server is showing relatively high execution time for this INSERT statement, like 0.4 seconds mean time. Is this form of blended INSERT with UPDATE and RETURNING slower than doing a direct insert?~~ (was misreading data, these are milliseconds, not seconds)
Every time a remote federation Upvote on a comment comes in to Lemmy, it executes this statement.
This query seems a little weird to me, though I don't know that this would explain it being slow. Why is it setting the person ID and comment ID in the
ON CONFLICT DO UPDATE
clause, given that the clause's intent is to update the score for an existing person/comment pair? Similarly, why update the post ID in that case? Would upvoting a comment ever cause it to move to a different post if there was already an upvote?I'd expect this to look more like
I did some digging into the database and got a list of indexes, and it is probably slow as it has two keys and 3 indexes, 5 total:
I'm currently in the process of setting up a load testing environment for lemmy, targeted at the DB for the first draft. Using powa and prometheus to look at the real impact. I would assume most of these udpates are HOT, but i'm not sure yet.
BTW: You have a DM already ;)