3

I've been searching a lot on this topic but I'm stuck at optimizing a query.

I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll see the execution time increase by a lot.

The problem lies in the recursive query, see the explain on depesz here.

The results I want is comments and child comments per topic, ordered by creation. There will always only be 1 level deep reply. So 1 parent and 1 child only.

Comment
  - child (likes 0)
  - child (likes 1)
Comment
  - child (likes 2)
  - etc...

The tables involved are:

CREATE TABLE comments
(
    comment_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    from_user_id integer NOT NULL,
    fk_topic_id integer NOT NULL,
    comment_text text COLLATE pg_catalog."default",
    parent_comment_id bigint,
    created timestamp without time zone NOT NULL,
    CONSTRAINT pk_comments PRIMARY KEY (comment_id, created)
);

CREATE TABLE comment_likes
(
    comment_like_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    fk_comment_id bigint NOT NULL,
    fk_user_id integer NOT NULL,
    created timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT pk_comment_likes PRIMARY KEY (comment_like_id, created)
);

CREATE TABLE users
(
    user_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_name text COLLATE pg_catalog."default",
    created timestamp without time zone NOT NULL,
    CONSTRAINT pk_users PRIMARY KEY (user_id)
);

Note that comments references itself with a foreign key parent_comment_id, I've omitted the foreign key relationship since the real implementation has partitioned tables (and you can't reference a partitioned table)

I've created a view for the recursive cte query:

CREATE OR REPLACE VIEW vw_comments AS
            SELECT comments.comment_id,
                comments.from_user_id,
                comments.fk_topic_id,
                comments.comment_text,
                comments.parent_comment_id,
                comments.created,
                users.user_name,
                COALESCE(cmt_likes.likes, 0::bigint) AS likes
           FROM comments
             LEFT JOIN users ON comments.from_user_id = users.user_id
             LEFT JOIN (SELECT count(*) AS likes,
                comment_likes.fk_comment_id
                   FROM comment_likes 
                         GROUP BY comment_likes.fk_comment_id) cmt_likes 
                         ON comments.comment_id = cmt_likes.fk_comment_id;

and this is the recursive query:

WITH RECURSIVE included_childs(comment_id, from_user_id, fk_topic_id, comment_text, parent_comment_id, created, user_name, likes) AS
    ((SELECT comment_id, from_user_id, fk_topic_id, comment_text, parent_comment_id, created, user_name, likes
    FROM vw_comments
    where
      parent_comment_id is null
      and fk_topic_id = 1
    order by created desc
    limit 20 offset 0)
  union all

    SELECT c.comment_id, c.from_user_id, c.fk_topic_id, c.comment_text, c.parent_comment_id, c.created, c.user_name, c.likes
    FROM included_childs ch, vw_comments c
    WHERE ch.comment_id = c.parent_comment_id
  )

SELECT*
FROM included_childs;

The results (for clearer view see the dbfiddle):

comment_id  from_user_id    fk_topic_id comment_text    parent_comment_id   created user_name   likes
8   2   1   1bulk test w like       2019-08-06 14:42:49.901169  elger   1
9   2   1   1bulk test      2019-08-06 14:42:49.901169  elger   0
16  2   1   1bulk test w like       2019-08-06 14:42:49.901169  elger   1
17  2   1   1bulk test      2019-08-06 14:42:49.901169  elger   0
24  2   1   1bulk test w like       2019-08-06 14:42:49.901169  elger   1
... etc

The results are correct (ie 20 parent comments with corresponding childs and counted likes), but it's slow with lots of records.

I migrated the db from sql server to postgres and the recursive cte idea is coming from there (and performs well in sql). Should I ditch the recursive query or can it be made more performant? Or are there other options?

update here is an explain with more data (420k comments), time increased to almost 1 sec. As explained in the comments below, this would starve connections in a high concurrent environment.

update 2 As @a_horse_with_no_name suggested, I'm doing the recursive query first without likes and user join, and join that later. That's almost twice as fast. See the new dbfiddle here and explain here. Not bad, but still more than 500ms with 420k records.

  • The execution plan shows that the query runs in 133 milliseconds - how fast do you need that to be? – a_horse_with_no_name Aug 6 at 14:08
  • @a_horse_with_no_name that's only with 70000 comments. Increase the amount of comments in the example to half million or so and it's 1 second. – Elger Mensonides Aug 6 at 14:09
  • Then please upload the slow plan (and how much faster than 1 second should that be?) – a_horse_with_no_name Aug 6 at 14:12
  • @a_horse_with_no_name, updated the question with a link. Around 100ms and I'm satisfied :P. But seriously, if hitting the db w more concurrent queries higher than that will starve connections. – Elger Mensonides Aug 6 at 14:17
  • 2
    I would probably do the recursive query first, and only then join the count of likes to that result so that you only do the counting for those that you actually return – a_horse_with_no_name Aug 6 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.