Postgres 12

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

DROP TABLE IF EXISTS objs,users;

CREATE  TABLE "objs"("number" Integer,"name" Text NOT NULL,  "price" Text NOT NULL );
CREATE   tABLE "users"("name" Text NOT NULL,"obj_number" Text NOT NULL );

INSERT INTO "objs" ("number","name","price") VALUES ( 1,'Small Red Apples','30' );
INSERT INTO "objs" ("number","name","price") VALUES ( 1,'Big Apples','50' );
INSERT INTO "objs" ("number","name","price") VALUES ( 2,'Small Bottle','24' );
INSERT INTO "objs" ("number","name","price") VALUES ( 2,'Big Bottle','60' );
INSERT INTO "objs" ("number","name","price") VALUES ( 1,'Small green Apples','45' );

INSERT INTO "users" ("name","obj_number") VALUES ( 'Mike','1' );
INSERT INTO "users" ("name","obj_number") VALUES ( 'Jow','2' );
INSERT INTO "users" ("name","obj_number") VALUES ( 'Piter','3' );

SELECT row_to_json(j) FROM (
SELECT
    u.name,
    u.obj_number,
    json_agg(json_build_object('name', o.name, 'price', o.price)) AS objs
FROM users u 
LEFT JOIN objs o ON u.obj_number = o.number::text
GROUP BY u.name,u.obj_number) j

DROP TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

row_to_json
{"name":"Piter","obj_number":"3","objs":[{"name" : null, "price" : null}]}
{"name":"Mike","obj_number":"1","objs":[{"name" : "Small Red Apples", "price" : "30"}, {"name" : "Big Apples", "price" : "50"}, {"name" : "Small green Apples", "price" : "45"}]}
{"name":"Jow","obj_number":"2","objs":[{"name" : "Small Bottle", "price" : "24"}, {"name" : "Big Bottle", "price" : "60"}]}

SELECT 3