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 transactions (customer TEXT, city TEXT, units INT);
INSERT INTO transactions VALUES ('Bob', 'Manchester', 10),
                                ('Chuck', 'Manchester', 20),
                                ('Bob', 'Concord', 10),
                                ('Tim', 'Manchester', 15),
                                ('Jane', 'Derry', 10),
                                ('Tim', 'Derry', 15),
                                ('Tim', 'Concord', 20),
                                ('Bob', 'Manchester', 20),
                                ('Chuck', 'Concord', 10);
                                

CREATE TABLE



INSERT 0 9


-- Incorrect results  
  SELECT customer
       , city
       , SUM(units) AS "units"
       , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER () , 2) AS "to report"
       , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER (PARTITION BY customer) , 2) AS "to customer"
       , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER (PARTITION BY city) , 2) AS "to city"
    FROM transactions
GROUP BY CUBE(customer, city)


customer
city
units
to report
to customer
to city


Bob
Concord
10
1.92
12.50
12.50

Bob
Manchester
30
5.77
37.50
23.08

Bob
null
40
7.69
50.00
15.38

Chuck
Concord
10
1.92
16.67
12.50

Chuck
Manchester
20
3.85
33.33
15.38

Chuck
null
30
5.77
50.00
11.54

Jane
Derry
10
1.92
50.00
20.00

Jane
null
10
1.92
50.00
3.85

Tim
Concord
20
3.85
20.00
25.00

Tim
Derry
15
2.88
15.00
30.00

Tim
null
50
9.62
50.00
19.23

Tim
Manchester
15
2.88
15.00
11.54

null
null
130
25.00
50.00
50.00

null
Manchester
65
12.50
25.00
50.00

null
Derry
25
4.81
9.62
50.00

null
Concord
40
7.69
15.38
50.00



SELECT 16


-- Answer
  SELECT customer
       , city
       , SUM(units) AS "units"
       , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER (PARTITION BY GROUPING(customer, city)) , 2) AS "to report"
       , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER (PARTITION BY GROUPING(customer, city), customer) , 2) AS "to customer"
       , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER (PARTITION BY GROUPING(customer, city), city) , 2) AS "to city"
    FROM transactions
GROUP BY CUBE(customer, city)


customer
city
units
to report
to customer
to city


Bob
Manchester
30
23.08
75.00
46.15

Bob
Concord
10
7.69
25.00
25.00

Chuck
Concord
10
7.69
33.33
25.00

Chuck
Manchester
20
15.38
66.67
30.77

Jane
Derry
10
7.69
100.00
40.00

Tim
Concord
20
15.38
40.00
50.00

Tim
Derry
15
11.54
30.00
60.00

Tim
Manchester
15
11.54
30.00
23.08

Bob
null
40
30.77
100.00
30.77

Chuck
null
30
23.08
100.00
23.08

Jane
null
10
7.69
100.00
7.69

Tim
null
50
38.46
100.00
38.46

null
Concord
40
30.77
30.77
100.00

null
Derry
25
19.23
19.23
100.00

null
Manchester
65
50.00
50.00
100.00

null
null
130
100.00
100.00
100.00



SELECT 16

customer	city	units	to report	to customer	to city
Bob	Concord	10	1.92	12.50	12.50
Bob	Manchester	30	5.77	37.50	23.08
Bob	null	40	7.69	50.00	15.38
Chuck	Concord	10	1.92	16.67	12.50
Chuck	Manchester	20	3.85	33.33	15.38
Chuck	null	30	5.77	50.00	11.54
Jane	Derry	10	1.92	50.00	20.00
Jane	null	10	1.92	50.00	3.85
Tim	Concord	20	3.85	20.00	25.00
Tim	Derry	15	2.88	15.00	30.00
Tim	null	50	9.62	50.00	19.23
Tim	Manchester	15	2.88	15.00	11.54
null	null	130	25.00	50.00	50.00
null	Manchester	65	12.50	25.00	50.00
null	Derry	25	4.81	9.62	50.00
null	Concord	40	7.69	15.38	50.00

customer	city	units	to report	to customer	to city
Bob	Manchester	30	23.08	75.00	46.15
Bob	Concord	10	7.69	25.00	25.00
Chuck	Concord	10	7.69	33.33	25.00
Chuck	Manchester	20	15.38	66.67	30.77
Jane	Derry	10	7.69	100.00	40.00
Tim	Concord	20	15.38	40.00	50.00
Tim	Derry	15	11.54	30.00	60.00
Tim	Manchester	15	11.54	30.00	23.08
Bob	null	40	30.77	100.00	30.77
Chuck	null	30	23.08	100.00	23.08
Jane	null	10	7.69	100.00	7.69
Tim	null	50	38.46	100.00	38.46
null	Concord	40	30.77	30.77	100.00
null	Derry	25	19.23	19.23	100.00
null	Manchester	65	50.00	50.00	100.00
null	null	130	100.00	100.00	100.00