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

CREATE TABLE buildings (
    id SERIAL PRIMARY KEY UNIQUE,
    name character varying(20) NOT NULL UNIQUE,
    document jsonb
);
CREATE VIEW v_rooms AS  
SELECT buildings.id AS building_id,
    buildings.name AS building_name,
    jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS level_number,
    ((jsonb_array_elements(jsonb_array_elements(buildings.document -> 'levels'::text) -> 'rooms'::text) -> 'meta'::text) ->> 'sqft'::text)::numeric AS sqft
FROM buildings
GROUP BY buildings.id;
CREATE OR REPLACE VIEW v_levels AS
SELECT
  buildings.id AS building_id,
  buildings.name AS building_name,
  jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS number,
  sqft
FROM buildings
JOIN (
  SELECT building_id, level_number, sum(sqft) as sqft
  FROM v_rooms
  GROUP BY ( v_rooms.building_id, v_rooms.level_number )
) v_rooms ON ( v_rooms.building_id = buildings.id and v_rooms.level_number = level_number );
INSERT INTO buildings ( name, document )
VALUES (
 'my blding',
 '{
  "levels": [
    {
      "meta": {
        "level_number": 0
      },
      "rooms": [
        { 
          "meta": {
          "sqft": 500
          }
        }, 
          {
          "meta": {
            "sqft": 800
          }
        }
      ]
    },
    {
      "meta": {
        "level_number": 1
      },
      "rooms": [{
        "meta": {
          "sqft": 900
        }
      }, {
        "meta": {
          "sqft": 900
        }
      }]

    }

  ]
}

'

)
1 rows affected

SELECT * FROM buildings


id
name
document


1
my blding
{"levels": [{"meta": {"level_number": 0}, "rooms": [{"meta": {"sqft": 500}}, {"meta": {"sqft": 800}}]}, {"meta": {"level_number": 1}, "rooms": [{"meta": {"sqft": 900}}, {"meta": {"sqft": 900}}]}]}


SELECT * FROM v_rooms


building_id
building_name
level_number
sqft


1
my blding
0
500

1
my blding
0
800

1
my blding
1
900

1
my blding
1
900


SELECT * FROM v_levels


building_id
building_name
number
sqft


1
my blding
0
1300

1
my blding
1
1300

1
my blding
0
1800

1
my blding
1
1800

building_id	building_name	number	sqft
1	my blding	0	1300
1	my blding	1	1300
1	my blding	0	1800
1	my blding	1	1800