Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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 INDEX ix_comments_parent_comment_id
    ON comments USING btree
    (parent_comment_id)
    TABLESPACE pg_default;
CREATE INDEX ix_comments_fk_topic_id
    ON comments USING btree
    (fk_topic_id)
    TABLESPACE pg_default;
CREATE INDEX ix_comments_from_user_id
    ON comments USING btree
    (from_user_id)
    TABLESPACE pg_default;

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 INDEX ix_comment_likes_fk_comment_id_fk_user_id
    ON comment_likes USING btree
    (fk_comment_id, fk_user_id)
    TABLESPACE pg_default;
CREATE INDEX ix_comment_likes_fk_user_id
    ON comment_likes USING btree
    (fk_user_id)
    TABLESPACE pg_default;
    
    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)
)

ERROR:  permission denied for tablespace pg_default


-- insert sample data
-- 20k parent comments + 60k childs + 10k likes
DO 
$do$
	DECLARE
	   counter INTEGER := 0; 
	begin
		FOR counter IN 1..60000 LOOP
	
			   with cte as (
				insert into comments (from_user_id, fk_topic_id, comment_text, parent_comment_id, created)
				values 
					(2, 1, '1bulk test', null, now()) 
				returning comments.comment_id
			)
			insert into comments (from_user_id, fk_topic_id, comment_text, parent_comment_id, created)
				values 
					(3, 1, '1child bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child2 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child3 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child4 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child5 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child6 bulk', (select cte.comment_id from cte), now());

                        with cte as (
				insert into comments (from_user_id, fk_topic_id, comment_text, parent_comment_id, created)
				values 
					(2, 1, '1bulk test w like', null, now()) 
				returning comments.comment_id
			)
 	                insert into comment_likes (fk_comment_id, fk_user_id)
			              values ((select cte.comment_id from cte),3);
			     	
	
	    end LOOP;
	end
$do$;

-- insert 10000 users
DO
$do$
	DECLARE
	   counter INTEGER := 0; 
	begin
		FOR counter IN 1..10000 loop
		insert into users (user_name, created)
			values ('elger', now());
					     	
	    end LOOP;
	end
$do$;
ERROR:  relation "comments" does not exist
LINE 2:     insert into comments (from_user_id, fk_topic_id, comment...
                        ^
QUERY:  with cte as (
				insert into comments (from_user_id, fk_topic_id, comment_text, parent_comment_id, created)
				values 
					(2, 1, '1bulk test', null, now()) 
				returning comments.comment_id
			)
			insert into comments (from_user_id, fk_topic_id, comment_text, parent_comment_id, created)
				values 
					(3, 1, '1child bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child2 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child3 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child4 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child5 bulk', (select cte.comment_id from cte), now()),
			     	(3, 1, '1child6 bulk', (select cte.comment_id from cte), now())
CONTEXT:  PL/pgSQL function inline_code_block line 7 at SQL statement


explain (analyse,buffers)
WITH RECURSIVE included_childs(comment_id, from_user_id, fk_topic_id, comment_text, parent_comment_id, created) AS
	((SELECT comment_id, from_user_id, fk_topic_id, comment_text, parent_comment_id, created
	FROM 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
	FROM included_childs ch, comments c
	WHERE ch.comment_id = c.parent_comment_id
  )

SELECT c.*, u.user_name, 
     COALESCE(cmt_likes.likes, 0::bigint) AS likes
FROM included_childs c 
left join users u on c.from_user_id = u.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 c.comment_id = cmt_likes.fk_comment_id;
ERROR:  relation "comments" does not exist
LINE 4:  FROM comments
              ^