Postgres 13

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 IF NOT EXISTS preprocess_things
(
    preprocess_id SERIAL NOT NULL,
    arrive_date timestamp without time zone NOT NULL,
    arrive_location character varying COLLATE pg_catalog."default" NOT NULL,
    data jsonb NOT NULL,
    CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
    CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
);
INSERT INTO preprocess_things (arrive_date, arrive_location, data) VALUES 
  ('2018-05-30 05:00:00', 'location_00', '{"3": 2}'),
  ('2018-05-30 10:00:00', 'location_00', '{"3": 8, "4": 7}'),
  ('2018-05-28 10:00:00', 'location_00', '{"5": 3, "2": 1}'),
  ('2018-05-28 11:00:00', 'location_00', '{"4": 1, "2": 8}'),
  ('2018-06-02 09:00:00', 'location_00', '{"4": 1, "5": 8}'),
  ('2018-06-02 08:00:00', 'location_00', '{"1": 1}');
6 rows affected

SELECT
  DATE_TRUNC('month', arrive_date) AS grouped_date,
  LOWER(arrive_location) AS location,
  json_build_object(
    '1', SUM((data->'1')::int),
    '2', SUM((data->'2')::int),
    '3', SUM((data->'3')::int),
    '4', SUM((data->'4')::int),
    '5', SUM((data->'5')::int)
    ) AS data
FROM preprocess_things
GROUP BY grouped_date,
  location


grouped_date
location
data


2018-06-01 00:00:00
location_00
{"1" : 1, "2" : null, "3" : null, "4" : 1, "5" : 8}

2018-05-01 00:00:00
location_00
{"1" : null, "2" : 9, "3" : 10, "4" : 8, "5" : 3}

grouped_date	location	data
2018-06-01 00:00:00	location_00	{"1" : 1, "2" : null, "3" : null, "4" : 1, "5" : 8}
2018-05-01 00:00:00	location_00	{"1" : null, "2" : 9, "3" : 10, "4" : 8, "5" : 3}