Postgres 15

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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table content (

    id                  integer,
    title text,
    props               jsonb,
    container_id        integer,

    -- PRIMARY KEY

    constraint pk_content

        primary key(id)

);

insert into content(id, title, props, container_id) values (1,'HOME1','{"a": "1"}', NULL);
insert into content(id, title, props, container_id) values (2,'HOME2','{"a": "1", "b": "1"}', NULL);
insert into content(id, title, props, container_id) values (3,'HOME21', '{"c": "3", "d":"3", "foo": "bar"}', 2);
insert into content(id, title, props, container_id) values (4,'HOME22', '{"c": "4", "d":"4"}', 2);
insert into content(id, title, props, container_id) values (5,'HOME31', '{"c": "XXX", "d":"4"}', 4);
;




CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select id, container_id, props, (WITH RECURSIVE parents AS (
            SELECT id, container_id, props, 1 as level FROM content WHERE id=5
                UNION ALL
            SELECT c.id, c.container_id, c.props, level+1 FROM content c
                                JOIN parents p ON p.container_id=c.id) select jsonb_object_agg('foo', props::jsonb) from parents where props is not null) from content where id=5;


id
container_id
props
jsonb_object_agg


5
4
{"c": "XXX", "d": "4"}
{"foo": {"a": "1", "b": "1"}}



SELECT 1


WITH RECURSIVE parents AS (
    (
        SELECT id, container_id, props, 1 AS level FROM content WHERE id = 5
    )
    UNION ALL
    (
        SELECT c.id, c.container_id, c.props, level + 1 FROM content AS c JOIN parents AS p ON p.container_id = c.id
    )
)
SELECT
    *
FROM
    parents p,
    jsonb_each(p.props) as x
;


id
container_id
props
level
key
value


5
4
{"c": "XXX", "d": "4"}
1
c
"XXX"

5
4
{"c": "XXX", "d": "4"}
1
d
"4"

4
2
{"c": "4", "d": "4"}
2
c
"4"

4
2
{"c": "4", "d": "4"}
2
d
"4"

2
null
{"a": "1", "b": "1"}
3
a
"1"

2
null
{"a": "1", "b": "1"}
3
b
"1"



SELECT 6


WITH RECURSIVE parents AS (
    (
        SELECT id, container_id, props, 1 AS level FROM content WHERE id = 5
    )
    UNION ALL
    (
        SELECT c.id, c.container_id, c.props, level + 1 FROM content AS c JOIN parents AS p ON p.container_id = c.id
    )
)
SELECT
    jsonb_object_agg( x.key, x.value ORDER BY p.level DESC )
FROM
    parents p,
    jsonb_each(p.props) as x
;


jsonb_object_agg


{"a": "1", "b": "1", "c": "XXX", "d": "4"}



SELECT 1

id	container_id	props	level	key	value
5	4	{"c": "XXX", "d": "4"}	1	c	"XXX"
5	4	{"c": "XXX", "d": "4"}	1	d	"4"
4	2	{"c": "4", "d": "4"}	2	c	"4"
4	2	{"c": "4", "d": "4"}	2	d	"4"
2	null	{"a": "1", "b": "1"}	3	a	"1"
2	null	{"a": "1", "b": "1"}	3	b	"1"