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.