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

CREATE TABLE IF NOT EXISTS visits_agg (
  id          serial primary key,
  hour        timestamp without time zone default (now() at time zone 'utc'),
  hostname    text not null,
  visits      integer default 0,
  referrers   jsonb not null default '{}'::jsonb,
  paths       jsonb not null default '{}'::jsonb,
  widths      jsonb not null default '{}'::jsonb
);
INSERT INTO "visits_agg" ("hour", "hostname", "visits", "referrers", "paths", "widths") VALUES
('2019-01-16 09:00:00', 'example.com', '6', '{"example2.com": 4}', '{"/": 5, "/about": 1}', '{"1280": 5, "1283": 1}'),
('2019-01-16 14:00:00', 'example.com', '7', '{"google": 6}', '{"/": 1, "/about": 3, "/article/title1": 1, "/article/title2": 2}', '{"360": 3, "1398": 1, "1680": 1, "2560": 1}'),
('2019-01-16 15:00:00', 'example.com', '6', '{"google": 6}', '{"/about": 1, "/article/title1": 1, "/article/title2": 3, "/": 1}', '{"360": 3, "1280": 2, "1920": 1}'),
('2019-01-16 16:00:00', 'example.com', '6', '{"facebook": 1, "example2.com": 1}', '{"/": 4, "/about": 1, "/article/title1": 1}', '{"360": 1, "1440": 2, "1680": 2, "1920": 1}');
4 rows affected

SELECT row_to_json(t0, true)
FROM
    (
    SELECT t1.hostname, t1.visits, t2.referrers, t3.paths, t4.widths
    FROM   (SELECT   hostname, SUM(visits) as visits
            FROM     visits_agg
            GROUP BY hostname) t1
    JOIN   (SELECT   hostname, 
                     jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
            FROM     (SELECT hostname, r.key as ref_name, 
                                       SUM(r.value::int) as ref_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(referrers) r
                      GROUP BY hostname, r.key) t1
           GROUP BY hostname) t2
           ON t1.hostname = t2.hostname
    JOIN   (SELECT   hostname, 
                     jsonb_agg(jsonb_build_object(path_name, path_value)) as paths
            FROM     (SELECT hostname, p.key as path_name, 
                                       SUM(p.value::int) as path_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(paths) p
                      GROUP BY hostname, p.key) t1
            GROUP BY hostname) t3
            ON t1.hostname = t3.hostname
    JOIN   (SELECT   hostname, 
                     jsonb_agg(jsonb_build_object(width_name, width_value)) as widths
            FROM     (SELECT hostname, w.key as width_name, 
                                       SUM(w.value::int) as width_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(widths) w
                      GROUP BY hostname, w.key) t1
            GROUP BY hostname) t4
            ON t1.hostname = t4.hostname
    ) t0;


row_to_json


{"hostname":"example.com",
 "visits":25,
 "referrers":[{"google": 12}, {"facebook": 1}, {"example2.com": 5}],
 "paths":[{"/about": 6}, {"/article/title2": 5}, {"/article/title1": 3}, {"/": 11}],
 "widths":[{"1680": 3}, {"1398": 1}, {"2560": 1}, {"360": 7}, {"1280": 7}, {"1440": 2}, {"1920": 2}, {"1283": 1}]}


SELECT 
    hostname, r.*, p.*
FROM
    visits_agg
CROSS JOIN LATERAL
    (SELECT r.key, SUM(r.value::int) rvalue
     FROM jsonb_each(referrers) r
     GROUP BY r.key) r
CROSS JOIN LATERAL
    (SELECT r.key, SUM(r.value::int) rvalue 
     FROM jsonb_each(paths) r 
     GROUP BY r.key) p


hostname
key
rvalue
key
rvalue


example.com
example2.com
4
/about
1

example.com
example2.com
4
/
5

example.com
google
6
/about
3

example.com
google
6
/article/title1
1

example.com
google
6
/article/title2
2

example.com
google
6
/
1

example.com
google
6
/about
1

example.com
google
6
/article/title1
1

example.com
google
6
/article/title2
3

example.com
google
6
/
1

example.com
facebook
1
/about
1

example.com
facebook
1
/article/title1
1

example.com
facebook
1
/
4

example.com
example2.com
1
/about
1

example.com
example2.com
1
/article/title1
1

example.com
example2.com
1
/
4

hostname	key	rvalue	key	rvalue
example.com	example2.com	4	/about	1
example.com	example2.com	4	/	5
example.com	google	6	/about	3
example.com	google	6	/article/title1	1
example.com	google	6	/article/title2	2
example.com	google	6	/	1
example.com	google	6	/about	1
example.com	google	6	/article/title1	1
example.com	google	6	/article/title2	3
example.com	google	6	/	1
example.com	facebook	1	/about	1
example.com	facebook	1	/article/title1	1
example.com	facebook	1	/	4
example.com	example2.com	1	/about	1
example.com	example2.com	1	/article/title1	1
example.com	example2.com	1	/	4