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', '[{"url":"example2.com", "value":4}]', '{"/": 5, "/about": 1}', '{"1280": 5, "1283": 1}'),
('2019-01-16 14:00:00', 'example.com', '7', '[{"url":"google", "value":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', '[{"url":"google", "value":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', '[{"url":"facebook", "value":1}, {"url":"example2.com", "value":1}]', '{"/": 4, "/about": 1, "/article/title1": 1}', '{"360": 1, "1440": 2, "1680": 2, "1920": 1}');
4 rows affected

SELECT * FROM visits_agg;


id
hour
hostname
visits
referrers
paths
widths


1
2019-01-16 09:00:00
example.com
6
[{"url": "example2.com", "value": 4}]
{"/": 5, "/about": 1}
{"1280": 5, "1283": 1}

2
2019-01-16 14:00:00
example.com
7
[{"url": "google", "value": 6}]
{"/": 1, "/about": 3, "/article/title1": 1, "/article/title2": 2}
{"360": 3, "1398": 1, "1680": 1, "2560": 1}

3
2019-01-16 15:00:00
example.com
6
[{"url": "google", "value": 6}]
{"/": 1, "/about": 1, "/article/title1": 1, "/article/title2": 3}
{"360": 3, "1280": 2, "1920": 1}

4
2019-01-16 16:00:00
example.com
6
[{"url": "facebook", "value": 1}, {"url": "example2.com", "value": 1}]
{"/": 4, "/about": 1, "/article/title1": 1}
{"360": 1, "1440": 2, "1680": 2, "1920": 1}


SELECT 
    hostname,
    SUM(VISITS) as visits
FROM
    visits_agg
GROUP BY
    hostname;


hostname
visits


example.com
25


SELECT
    hostname,
    ref->'url' as ref_url, 
    SUM((ref->'value')::int) as ref_value
FROM
    visits_agg
CROSS JOIN LATERAL
    jsonb_array_elements(referrers) ref
GROUP BY
    hostname,
    ref_url;


hostname
ref_url
ref_value


example.com
"google"
12

example.com
"facebook"
1

example.com
"example2.com"
5


SELECT
    jsonb_build_object(ref_url::text, ref_value::int)
FROM
(
SELECT
    hostname,
    ref->'url' as ref_url, 
    SUM((ref->'value')::int) as ref_value
FROM
    visits_agg
CROSS JOIN LATERAL
    jsonb_array_elements(referrers) ref
GROUP BY
    hostname,
    ref_url
) t
GROUP BY
    hostname
ERROR:  column "t.ref_url" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:     jsonb_build_object(ref_url::text, ref_value::int)
                               ^

id	hour	hostname	visits	referrers	paths	widths
1	2019-01-16 09:00:00	example.com	6	[{"url": "example2.com", "value": 4}]	{"/": 5, "/about": 1}	{"1280": 5, "1283": 1}
2	2019-01-16 14:00:00	example.com	7	[{"url": "google", "value": 6}]	{"/": 1, "/about": 3, "/article/title1": 1, "/article/title2": 2}	{"360": 3, "1398": 1, "1680": 1, "2560": 1}
3	2019-01-16 15:00:00	example.com	6	[{"url": "google", "value": 6}]	{"/": 1, "/about": 1, "/article/title1": 1, "/article/title2": 3}	{"360": 3, "1280": 2, "1920": 1}
4	2019-01-16 16:00:00	example.com	6	[{"url": "facebook", "value": 1}, {"url": "example2.com", "value": 1}]	{"/": 4, "/about": 1, "/article/title1": 1}	{"360": 1, "1440": 2, "1680": 2, "1920": 1}

hostname	ref_url	ref_value
example.com	"google"	12
example.com	"facebook"	1
example.com	"example2.com"	5