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.

you are viewing a single comment's thread
view the rest of the comments

[–] [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 ;)