Postgres 9.3

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?.

select version();


version


PostgreSQL 9.3.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit



SELECT 1


CREATE TABLE "public"."dimensionvalue" (
    "name" text NOT NULL,
    "id" int4,
    "dimensionvalueid" int4,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."valuation" (
    "id" int4,
    "volume" int4,
    "dimensionvalueid" int4,
    PRIMARY KEY ("id")
);

INSERT INTO dimensionvalue (id, name, dimensionvalueid)
VALUES (1, 'A1', null),
(2, 'A1:1', 1),
(3, 'A1:1:1', 2),
(4, 'A1:1:1:1', 3),
(5, 'A1:1:1:2', 3),
(6, 'B1', null),
(7, 'B1:1', 6),
(8, 'B1:2', 6),
(9, 'B1:2:1', 8);

INSERT INTO valuation (id, volume, dimensionvalueid)
VALUES (1, 200, 2),
(2, 200, 3),
(3, 200, 4),
(4, 100, 5),

(5, 100, 7),
(6, 100, 8);

INSERT 0 6


SELECT
    d.*,
    v.volume,
    (
        WITH RECURSIVE cte AS (
                SELECT 
                    dd.id AS branch_id,
                    dd.id
                FROM dimensionvalue dd
                WHERE dd.id = d.id
                UNION ALL
                SELECT
                    cte.branch_id,
                    dd.id
                FROM dimensionvalue dd
                JOIN cte ON dd.dimensionvalueid = cte.id
        )
        SELECT SUM(v.volume)
        FROM cte
        JOIN valuation v ON v.dimensionvalueid = cte.id
        GROUP BY cte.branch_id
    ) AS totals
FROM dimensionvalue d
LEFT JOIN valuation v ON v.dimensionvalueid = d.id
ORDER BY d.name;


name
id
dimensionvalueid
volume
totals


A1
1
null
null
700

A1:1
2
1
200
700

A1:1:1
3
2
200
500

A1:1:1:1
4
3
200
200

A1:1:1:2
5
3
100
100

B1
6
null
null
200

B1:1
7
6
100
100

B1:2
8
6
100
100

B1:2:1
9
8
null
null



SELECT 9


WITH RECURSIVE hierarchy AS (
    SELECT
	d.id,
	d.name,
	d.dimensionvalueid,
	0 AS level,
        CAST(d.id AS varchar(50)) AS order_sequence
    FROM dimensionvalue d
    WHERE d.dimensionvalueid IS NULL
    UNION ALL
    SELECT
	e.id,
	e.name,
	e.dimensionvalueid,
	hierarchy.level + 1 AS level,
	CAST(hierarchy.order_sequence || '_' || CAST(hierarchy.id AS VARCHAR(50)) AS VARCHAR(50)) AS order_sequence
    FROM hierarchy
    JOIN dimensionvalue e ON e.dimensionvalueid = hierarchy.id
)
SELECT
    RIGHT('-----------', h.level * 5) || h.name || ' ' AS parent_child_tree, 
    h.*,
    v.volume,
    (
        WITH RECURSIVE cte AS (
                SELECT 
                    dd.id AS branch_id,
                    dd.id
                FROM dimensionvalue dd
                WHERE dd.id = h.id
                UNION ALL
                SELECT
                    cte.branch_id,
                    dd.id
                FROM dimensionvalue dd
                JOIN cte ON dd.dimensionvalueid = cte.id
        )
        SELECT SUM(v.volume)
        FROM cte
        JOIN valuation v ON v.dimensionvalueid = cte.id
        GROUP BY cte.branch_id
    ) AS totals
FROM hierarchy h
LEFT JOIN valuation v ON v.dimensionvalueid = h.id
ORDER BY h.name


parent_child_tree
id
name
dimensionvalueid
level
order_sequence
volume
totals


A1
1
A1
null
0
1
null
700

-----A1:1
2
A1:1
1
1
1_1
200
700

----------A1:1:1
3
A1:1:1
2
2
1_1_2
200
500

-----------A1:1:1:1
4
A1:1:1:1
3
3
1_1_2_3
200
200

-----------A1:1:1:2
5
A1:1:1:2
3
3
1_1_2_3
100
100

B1
6
B1
null
0
6
null
200

-----B1:1
7
B1:1
6
1
6_6
100
100

-----B1:2
8
B1:2
6
1
6_6
100
100

----------B1:2:1
9
B1:2:1
8
2
6_6_8
null
null



SELECT 9

name	id	dimensionvalueid	volume	totals
A1	1	null	null	700
A1:1	2	1	200	700
A1:1:1	3	2	200	500
A1:1:1:1	4	3	200	200
A1:1:1:2	5	3	100	100
B1	6	null	null	200
B1:1	7	6	100	100
B1:2	8	6	100	100
B1:2:1	9	8	null	null

parent_child_tree	id	name	dimensionvalueid	level	order_sequence	volume	totals
A1	1	A1	null	0	1	null	700
-----A1:1	2	A1:1	1	1	1_1	200	700
----------A1:1:1	3	A1:1:1	2	2	1_1_2	200	500
-----------A1:1:1:1	4	A1:1:1:1	3	3	1_1_2_3	200	200
-----------A1:1:1:2	5	A1:1:1:2	3	3	1_1_2_3	100	100
B1	6	B1	null	0	6	null	200
-----B1:1	7	B1:1	6	1	6_6	100	100
-----B1:2	8	B1:2	6	1	6_6	100	100
----------B1:2:1	9	B1:2:1	8	2	6_6_8	null	null