PostgreSQL

22 readers

1 users here now

founded 1 year ago

MODERATORS

[email protected]

Lemmy PostgreSQL - can someone please help deconstruct this INSERT SQL: INSERT INTO "comment_like"... (lemmy.ml)

submitted 1 year ago* (last edited 1 year ago) by [email protected] to c/[email protected]

6 comments fedilink hide all child comments

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.

top 3 comments

sorted by: hot top controversial new old

[–] [email protected] 1 points 1 year ago (1 children)

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

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"

[–] [email protected] 1 points 1 year ago* (last edited 1 year ago) (1 children)

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)

[–] vapeloki 1 points 1 year ago

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

load more comments