Postgres 13

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 categories (
    id integer NOT NULL,
    name text NOT NULL,
    description text,
    slug text NOT NULL,
    parent_id integer
);

INSERT INTO categories (id, name, description, slug, parent_id) VALUES (1, 'Sport', NULL, 'sport', NULL);
INSERT INTO categories (id, name, description, slug, parent_id) VALUES (2, 'Running', 'All plans related to running.', 'running', 1);
INSERT INTO categories (id, name, description, slug, parent_id) VALUES (999, 'Sprinting', 'sprinting is fast running', 'sprinting', 2);

SELECT * FROM categories

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1




id
name
description
slug
parent_id


1
Sport
null
sport
null

2
Running
All plans related to running.
running
1

999
Sprinting
sprinting is fast running
sprinting
2



SELECT 3


WITH RECURSIVE hierarchy AS (
    SELECT id, parent_id
    FROM categories 
    WHERE id = 999
    
    UNION
    
    SELECT
        c.id, c.parent_id
    FROM categories c
    JOIN hierarchy h ON h.parent_id = c.id
)
SELECT * FROM hierarchy


id
parent_id


999
2

2
1

1
null



SELECT 3


WITH RECURSIVE hierarchy AS (
    SELECT id, parent_id
    FROM categories 
    WHERE id = 999
    
    UNION
    
    SELECT
        c.id, c.parent_id
    FROM categories c
    JOIN hierarchy h ON h.parent_id = c.id
),
jsonbuilder AS (
    SELECT 
        c.id, 
        h.parent_id,
        jsonb_build_object('id', c.id, 'name', c.name, 'description', c.description, 'slug', c.slug) as jsondata
    FROM hierarchy h
    JOIN categories c ON c.id = h.id
    WHERE h.parent_id IS NULL
    
    UNION
    
    SELECT
        c.id,
        h.parent_id,
        jsonb_build_object('id', c.id, 'name', c.name, 'description', c.description, 'slug', c.slug, 'parent', j.jsondata)  
    FROM hierarchy h
    JOIN categories c ON c.id = h.id
    JOIN jsonbuilder j ON j.id = h.parent_id
)
SELECT 
    jsonb_pretty(jsondata)
FROM jsonbuilder
WHERE id = 999


jsonb_pretty


{
    "id": 999,
    "name": "Sprinting",
    "slug": "sprinting",
    "parent": {
        "id": 2,
        "name": "Running",
        "slug": "running",
        "parent": {
            "id": 1,
            "name": "Sport",
            "slug": "sport",
            "description": null
        },
        "description": "All plans related to running."
    },
    "description": "sprinting is fast running"
}



SELECT 1

id	name	description	slug	parent_id
1	Sport	null	sport	null
2	Running	All plans related to running.	running	1
999	Sprinting	sprinting is fast running	sprinting	2