INSERT INTO "comment_like" ("person_id", "comment_id", "post_id", "score") VALUES (, , , ) ON CONFLICT ("comment_id", "person_id") DO UPDATE SET "person_id" = , "comment_id" = , "post_id" = , "score" = 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 UPDATEclause, 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
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 "score" = EXCLUDED.score RETURNING "comment_like"."id", "comment_like"."person_id", "comment_like"."comment_id", "comment_like"."post_id", "comment_like"."score", "comment_like"."published"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:
public comment_like idx_comment_like_comment null CREATE INDEX idx_comment_like_comment ON public.comment_like USING btree (comment_id) public comment_like idx_comment_like_post null CREATE INDEX idx_comment_like_post ON public.comment_like USING btree (post_id) public comment_like comment_like_pkey null CREATE UNIQUE INDEX comment_like_pkey ON public.comment_like USING btree (id) public comment_like comment_like_comment_id_person_id_key null CREATE UNIQUE INDEX comment_like_comment_id_person_id_key ON public.comment_like USING btree (comment_id, person_id) public comment_like idx_comment_like_person null CREATE INDEX idx_comment_like_person ON public.comment_like USING btree (person_id)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 ;)
