Postgres 9.6

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 users (
    id int,
    name text
);

INSERT INTO users VALUES
(1, 'Mike Tyson');

CREATE TABLE comments (
    id text,
    body text,
    user_id int,
    likes int
);

INSERT INTO comments VALUES
('a', 'foo', 1, 1),
('b', 'foofoo', 1, 232),
('c', 'foofoofoo', 1, 23232),
('d', 'fooFOO', 1, 53),
('e', 'cookies', 1, 864),
('f', 'bar', 1, 44),
('g', 'barbar', 1, 54),
('h', 'barBAR', 1, 222),
('i', 'more cookies', 1, 1);

CREATE TABLE replies (
    id text,
    parent_id text
);

INSERT INTO replies VALUES
('a', NULL),
('b', 'a'),
('c', 'b'),
('d', 'a'),
('e', NULL),
('f', NULL),
('g', 'f'),
('h', 'f'),
('i', NULL);
1 rows affected

9 rows affected

9 rows affected

        WITH RECURSIVE parent_tree AS (
            SELECT 
                id, 
                NULL::text[] as parent_id,
                array_append('{comments}'::text[], (row_number() OVER ())::text) as path, 
                rc.children  
            FROM replies r
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
            WHERE r.parent_id IS NULL 
        
            UNION
            
            SELECT 
                r.id, 
                array_append(pt.parent_id, r.parent_id), 
                array_append(array_append(pt.path, 'children'), (row_number() OVER (PARTITION BY pt.parent_id))::text),
                rc.children      
            FROM parent_tree pt
            JOIN replies r ON r.id = ANY(pt.children)
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
        ), json_objects AS (
           SELECT c.id, jsonb_build_object('children', '[]'::jsonb, 'comment_id', c.id, 'username', u.name, 'comment_body', c.body, 'comment_likes', c.likes) as jsondata
           FROM comments c
           LEFT JOIN users u ON u.id = c.user_id
        )
        SELECT 
            parent_id, 
            path,
            jsondata
        FROM parent_tree pt 
        LEFT JOIN json_objects jo ON pt.id = jo.id
        ORDER BY parent_id NULLS FIRST


parent_id
path
jsondata


null
{comments,4}
{"children": [], "username": "Mike Tyson", "comment_id": "i", "comment_body": "more cookies", "comment_likes": 1}

null
{comments,2}
{"children": [], "username": "Mike Tyson", "comment_id": "e", "comment_body": "cookies", "comment_likes": 864}

null
{comments,3}
{"children": [], "username": "Mike Tyson", "comment_id": "f", "comment_body": "bar", "comment_likes": 44}

null
{comments,1}
{"children": [], "username": "Mike Tyson", "comment_id": "a", "comment_body": "foo", "comment_likes": 1}

{a}
{comments,1,children,1}
{"children": [], "username": "Mike Tyson", "comment_id": "b", "comment_body": "foofoo", "comment_likes": 232}

{a}
{comments,1,children,2}
{"children": [], "username": "Mike Tyson", "comment_id": "d", "comment_body": "fooFOO", "comment_likes": 53}

{a,b}
{comments,1,children,1,children,1}
{"children": [], "username": "Mike Tyson", "comment_id": "c", "comment_body": "foofoofoo", "comment_likes": 23232}

{f}
{comments,3,children,3}
{"children": [], "username": "Mike Tyson", "comment_id": "g", "comment_body": "barbar", "comment_likes": 54}

{f}
{comments,3,children,4}
{"children": [], "username": "Mike Tyson", "comment_id": "h", "comment_body": "barBAR", "comment_likes": 222}


CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS $$
DECLARE
    _json_output jsonb;
    _temprow record;
BEGIN

    SELECT 
        jsonb_build_object('comments', '[]'::jsonb) 
    INTO _json_output;

    FOR _temprow IN
        WITH RECURSIVE parent_tree AS (
            SELECT 
                id, 
                NULL::text[] as parent_id,
                array_append('{comments}'::text[], (row_number() OVER ())::text) as path, 
                rc.children  
            FROM replies r
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
            WHERE r.parent_id IS NULL 
        
            UNION
            
            SELECT 
                r.id, 
                array_append(pt.parent_id, r.parent_id), 
                array_append(array_append(pt.path, 'children'), (row_number() OVER (PARTITION BY pt.parent_id))::text),
                rc.children      
            FROM parent_tree pt
            JOIN replies r ON r.id = ANY(pt.children)
            LEFT JOIN LATERAL (SELECT parent_id, ARRAY_AGG(id) as children FROM replies WHERE parent_id = r.id GROUP BY parent_id) rc ON rc.parent_id = r.id
        ), json_objects AS (
           SELECT c.id, jsonb_build_object('children', '[]'::jsonb, 'comment_id', c.id, 'username', u.name, 'comment_body', c.body, 'comment_likes', c.likes) as jsondata
           FROM comments c
           LEFT JOIN users u ON u.id = c.user_id
        )
        SELECT 
            parent_id, 
            path,
            jsondata
        FROM parent_tree pt 
        LEFT JOIN json_objects jo ON pt.id = jo.id
        ORDER BY parent_id NULLS FIRST
    LOOP
        SELECT jsonb_insert(_json_output, _temprow.path, _temprow.jsondata) INTO _json_output;
    END LOOP;

    RETURN _json_output;
END;
$$ LANGUAGE plpgsql;
SELECT jsonb_pretty(json_tree) FROM json_tree()


jsonb_pretty


{
    "comments": [
        {
            "children": [
            ],
            "username": "Mike Tyson",
            "comment_id": "i",
            "comment_body": "more cookies",
            "comment_likes": 1
        },
        {
            "children": [
                {
                    "children": [
                    ],
                    "username": "Mike Tyson",
                    "comment_id": "b",
                    "comment_body": "foofoo",
                    "comment_likes": 232
                },
                {
                    "children": [
                        {
                            "children": [
                            ],
                            "username": "Mike Tyson",
                            "comment_id": "c",
                            "comment_body": "foofoofoo",
                            "comment_likes": 23232
                        }
                    ],
                    "username": "Mike Tyson",
                    "comment_id": "d",
                    "comment_body": "fooFOO",
                    "comment_likes": 53
                }
            ],
            "username": "Mike Tyson",
            "comment_id": "a",
            "comment_body": "foo",
            "comment_likes": 1
        },
        {
            "children": [
            ],
            "username": "Mike Tyson",
            "comment_id": "e",
            "comment_body": "cookies",
            "comment_likes": 864
        },
        {
            "children": [
                {
                    "children": [
                    ],
                    "username": "Mike Tyson",
                    "comment_id": "g",
                    "comment_body": "barbar",
                    "comment_likes": 54
                },
                {
                    "children": [
                    ],
                    "username": "Mike Tyson",
                    "comment_id": "h",
                    "comment_body": "barBAR",
                    "comment_likes": 222
                }
            ],
            "username": "Mike Tyson",
            "comment_id": "f",
            "comment_body": "bar",
            "comment_likes": 44
        }
    ]
}

parent_id	path	jsondata
null	{comments,4}	{"children": [], "username": "Mike Tyson", "comment_id": "i", "comment_body": "more cookies", "comment_likes": 1}
null	{comments,2}	{"children": [], "username": "Mike Tyson", "comment_id": "e", "comment_body": "cookies", "comment_likes": 864}
null	{comments,3}	{"children": [], "username": "Mike Tyson", "comment_id": "f", "comment_body": "bar", "comment_likes": 44}
null	{comments,1}	{"children": [], "username": "Mike Tyson", "comment_id": "a", "comment_body": "foo", "comment_likes": 1}
{a}	{comments,1,children,1}	{"children": [], "username": "Mike Tyson", "comment_id": "b", "comment_body": "foofoo", "comment_likes": 232}
{a}	{comments,1,children,2}	{"children": [], "username": "Mike Tyson", "comment_id": "d", "comment_body": "fooFOO", "comment_likes": 53}
{a,b}	{comments,1,children,1,children,1}	{"children": [], "username": "Mike Tyson", "comment_id": "c", "comment_body": "foofoofoo", "comment_likes": 23232}
{f}	{comments,3,children,3}	{"children": [], "username": "Mike Tyson", "comment_id": "g", "comment_body": "barbar", "comment_likes": 54}
{f}	{comments,3,children,4}	{"children": [], "username": "Mike Tyson", "comment_id": "h", "comment_body": "barBAR", "comment_likes": 222}