Postgres 11

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



SELECT 1


CREATE TABLE foods (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE nutrients (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100)
);
CREATE TABLE foods_nutrients (
  food_id int REFERENCES foods(id) ON UPDATE CASCADE ON DELETE CASCADE,
  nutrient_id int REFERENCES nutrients(id) ON UPDATE CASCADE ON DELETE CASCADE,
  amount DECIMAL NOT NULL,
  CONSTRAINT food_nutrient_pk PRIMARY KEY (food_id, nutrient_id)
);

INSERT INTO foods(name)
VALUES  ('Apple'),
        ('Banana');
INSERT INTO nutrients(name)
VALUES  ('Carbohydrates'),
        ('Protein'),
        ('Fat');
INSERT INTO foods_nutrients(food_id, nutrient_id, amount)
VALUES  (1, 1, 14.0),
        (1, 2, 0.3),
        (1, 3, 0.2),
        (2, 1, 23.7),
        (2, 2, 1.1);

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 2



INSERT 0 3



INSERT 0 5


WITH fn1 AS
(
SELECT fn.food_id, f.name,
       JSONB_OBJECT_AGG( nutrient_id, amount ) AS nutrients                        
  FROM foods_nutrients fn 
  JOIN foods f 
    ON fn.food_id=f.id
  JOIN nutrients n 
    ON fn.nutrient_id=n.id
 GROUP BY fn.food_id, f.name  
)
SELECT JSONB_PRETTY(
                    JSONB_AGG(
                              JSONB_BUILD_OBJECT( 'id', food_id, 
                                                  'name', name,
                                                  'nutrients', nutrients) 
                             )
                   ) AS js                
  FROM fn1


js


[
    {
        "id": 1,
        "name": "Apple",
        "nutrients": {
            "1": 14.0,
            "2": 0.3,
            "3": 0.2
        }
    },
    {
        "id": 2,
        "name": "Banana",
        "nutrients": {
            "1": 23.7,
            "2": 1.1
        }
    }
]



SELECT 1