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 inventory (
  inventory_id serial PRIMARY KEY,
  arrive_date timestamp NOT NULL,
  arrive_location character varying NOT NULL,
  thing_type integer NOT NULL,
  quantity integer NOT NULL
);

INSERT INTO inventory (arrive_date, arrive_location, thing_type, quantity) VALUES
  ('2018-05-30 05:00:00-00', 'location_00', 3, 2)
, ('2018-05-30 06:00:00-00', 'location_00', 3, 8)
, ('2018-05-30 12:50:00-00', 'location_00', 5, 2)
, ('2018-05-30 13:40:00-00', 'location_00', 1, 3)
, ('2018-05-31 13:00:00-00', 'location_00', 4, 7)
, ('2018-05-31 18:00:00-00', 'location_00', 2, 3)
;

CREATE TABLE



INSERT 0 6


SELECT arrive_date, arrive_timeday, arrive_location
     , jsonb_object_agg(thing_type, total_things)
FROM  (
   SELECT arrive_date
        , arrive_timeday
        , arrive_location
        , thing_type
        , sum(quantity) AS total_things
   FROM   (
       SELECT date_trunc('day', arrive_date) AS arrive_date
            , case 
              when extract(hour from arrive_date) in (5, 6, 7 , 8, 9) then 0
              when extract(hour from arrive_date) in (10, 11) then 1
              when extract(hour from arrive_date) in (12, 13, 14) then 2
              when extract(hour from arrive_date) in (15, 16, 17 ,18 ,19, 20) then 4
              when extract(hour from arrive_date) in (21, 22, 23, 0, 1, 2, 3, 4) then 8
              end arrive_timeday
            , arrive_location
            , thing_type
            , quantity
       FROM   inventory
   ) inv
   GROUP  BY arrive_date, arrive_timeday, arrive_location, thing_type
   ) sub
GROUP  BY arrive_date, arrive_timeday, arrive_location
ORDER  BY arrive_date, arrive_timeday, arrive_location;


arrive_date
arrive_timeday
arrive_location
jsonb_object_agg


2018-05-30 00:00:00
0
location_00
{"3": 10}

2018-05-30 00:00:00
2
location_00
{"1": 3, "5": 2}

2018-05-31 00:00:00
2
location_00
{"4": 7}

2018-05-31 00:00:00
4
location_00
{"2": 3}



SELECT 4


CREATE TABLE hour_mapping (
  hour_from integer NOT NULL,
  hour_to integer NOT NULL,
  timeday integer NOT NULL,
  descpt character varying NOT NULL
);

INSERT INTO hour_mapping (hour_from, hour_to, timeday, descpt) VALUES 
  (5, 9, 0, 'morning')
, (10, 11, 1, 'mid_morning')
, (12, 14, 2, 'midday')
, (15, 20, 4, 'evening')
, (21, 23, 8, 'night')
, (0, 4, 8, 'night')
;

CREATE TABLE



INSERT 0 6


SELECT arrive_date, arrive_timeday, arrive_location
     , jsonb_object_agg(thing_type, total_things)
FROM  (
   SELECT date_trunc('day', arrive_date) AS arrive_date
        , m.timeday as arrive_timeday
        , arrive_location
        , thing_type
        , sum(quantity) AS total_things
   FROM  inventory inv, hour_mapping m
   WHERE extract(hour from inv.arrive_date) between m.hour_from and hour_to
   GROUP  BY arrive_date, arrive_timeday, arrive_location, thing_type
   ) sub
GROUP  BY arrive_date, arrive_timeday, arrive_location
ORDER  BY arrive_date, arrive_timeday, arrive_location;


arrive_date
arrive_timeday
arrive_location
jsonb_object_agg


2018-05-30 00:00:00
0
location_00
{"3": 8}

2018-05-30 00:00:00
2
location_00
{"1": 3, "5": 2}

2018-05-31 00:00:00
2
location_00
{"4": 7}

2018-05-31 00:00:00
4
location_00
{"2": 3}



SELECT 4

arrive_date	arrive_timeday	arrive_location	jsonb_object_agg
2018-05-30 00:00:00	0	location_00	{"3": 10}
2018-05-30 00:00:00	2	location_00	{"1": 3, "5": 2}
2018-05-31 00:00:00	2	location_00	{"4": 7}
2018-05-31 00:00:00	4	location_00	{"2": 3}

arrive_date	arrive_timeday	arrive_location	jsonb_object_agg
2018-05-30 00:00:00	0	location_00	{"3": 8}
2018-05-30 00:00:00	2	location_00	{"1": 3, "5": 2}
2018-05-31 00:00:00	2	location_00	{"4": 7}
2018-05-31 00:00:00	4	location_00	{"2": 3}