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 level_one_table (
id int,
column1 varchar(10)
);
insert into level_one_table values
(1, 'l1_va1'),
(2, 'l1_va2'),
(3, 'l1_va3'),
(4, 'l1_va4');
create table level_two_table (
id int,
fk_level_one_id int,
column2 varchar(10)
);
insert into level_two_table values
(1, 1, 'l2_va1'),
(2, 1, 'l2_va2'),
(3, 1, 'l2_va3'),
(4, 1, 'l2_va4'),
(5, 2, 'l2_va5'),
(6, 2, 'l2_va6'),
(7, 2, 'l2_va7'),
(8, 3, 'l2_va8'),
(9, 3, 'l2_va9'),
(10, 3, 'l2_va10'),
(11, 4, 'l2_va11'),
(12, 4, 'l2_va12'),
(13, 4, 'l2_va13');
create table level_three_table (
id int,
fk_level_two_id int,
column3 varchar(10)
CREATE TABLE
INSERT 0 4
CREATE TABLE
INSERT 0 13
CREATE TABLE
INSERT 0 13
SELECT
json_build_object(
'id', t1.id,
'level_two_table', level_two_table
) AS r0
FROM level_one_table t1
CROSS JOIN LATERAL (
SELECT
json_arrayagg(sub) AS level_two_table
FROM (
SELECT
t2.id,
json_build_object(
'id', t3.id
) AS level_three_table
FROM level_two_table t2
LEFT JOIN level_three_table t3 ON t2.id = t3.fk_level_two_id
WHERE t1.id = t2.fk_level_one_id
ORDER BY t2.column2 DESC LIMIT 4
) sub
) sub
ORDER BY t1.id DESC LIMIT 2
| r0 |
|---|
| {"id" : 4, "level_two_table" : [{"id":13,"level_three_table":{"id" : 13}}, {"id":12,"level_three_table":{"id" : 12}}, {"id":11,"level_three_table":{"id" : 11}}]} |
| {"id" : 3, "level_two_table" : [{"id":9,"level_three_table":{"id" : 9}}, {"id":8,"level_three_table":{"id" : 8}}, {"id":10,"level_three_table":{"id" : 10}}]} |
SELECT 2