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.

  • koreth@lemm.ee
    link
    fedilink
    English
    arrow-up
    2
    ·
    1 year ago

    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"
    
    • RoundSparrowOP
      link
      fedilink
      English
      arrow-up
      1
      ·
      edit-2
      1 year ago

      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)
      
    • RoundSparrowOP
      link
      fedilink
      English
      arrow-up
      1
      ·
      1 year ago

      Damn it - I made a mistake

      Ok, re-reading the documentation again, I made a major error interpreting these results.

      mean_exec_time double precision: Mean time spent executing the statement, in milliseconds

      All my statements about INSERT on Likes taking 1/3 of a second are wrong, it’s less than 1 millisecond. Although it sure doesn’t feel that fast when you are interactively using Lemmy 0.18 and pressing the Like button, it seems rather sluggish. I’ve almost never seen fractions of a milliseconds, but here it is.