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

select version();


version


PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE investors (
  id INT,
  email TEXT
);
INSERT INTO investors (id, email) VALUES (1, 'john@live.com');
INSERT INTO investors (id, email) VALUES (2, 'mike@live.com');
INSERT INTO investors (id, email) VALUES (3, 'sarah@live.com');
INSERT INTO investors (id, email) VALUES (4, 'dave@live.com');

CREATE TABLE investments (
  id INT,
  user_id INT,
  start_date DATE,
  end_date DATE,
  amount INT,
  percent INT
);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (1, 1, '2021-01-01', '2021-01-31', 10000, 20);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (2, 1, '2021-02-01', '2021-02-28', 5000, 20);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (3, 2, '2021-01-15', '2021-02-15', 20000, 40);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (4, 3, '2021-02-10', '2021-03-01', 30000, 15);
INSERT INTO investments (id, user_id, start_date, end_date, amount, percent) VALUES (5, 4, '2021-01-25', '2021-02-25', 6000, 45);

CREATE TABLE daily_stats (
  id INT,
  date DATE,
  profit INT
);
INSERT INTO daily_stats (id, date, profit) VALUES (1, '2021-01-01', 254);
INSERT INTO daily_stats (id, date, profit) VALUES (2, '2021-01-02', 188);
INSERT INTO daily_stats (id, date, profit) VALUES (3, '2021-01-03', 361);
INSERT INTO daily_stats (id, date, profit) VALUES (4, '2021-01-04', 469);
INSERT INTO daily_stats (id, date, profit) VALUES (5, '2021-01-05', 194);
INSERT INTO daily_stats (id, date, profit) VALUES (6, '2021-01-06', 155);
INSERT INTO daily_stats (id, date, profit) VALUES (7, '2021-01-07', 322);
INSERT INTO daily_stats (id, date, profit) VALUES (8, '2021-01-08', 354);
INSERT INTO daily_stats (id, date, profit) VALUES (9, '2021-01-09', 409);
INSERT INTO daily_stats (id, date, profit) VALUES (10, '2021-01-10', 132);
INSERT INTO daily_stats (id, date, profit) VALUES (11, '2021-01-11', 172);
INSERT INTO daily_stats (id, date, profit) VALUES (12, '2021-01-12', 176);
INSERT INTO daily_stats (id, date, profit) VALUES (13, '2021-01-13', 269);
INSERT INTO daily_stats (id, date, profit) VALUES (14, '2021-01-14', 195);
INSERT INTO daily_stats (id, date, profit) VALUES (15, '2021-01-15', 152);
INSERT INTO daily_stats (id, date, profit) VALUES (16, '2021-01-16', 460);
INSERT INTO daily_stats (id, date, profit) VALUES (17, '2021-01-17', 423);
INSERT INTO daily_stats (id, date, profit) VALUES (18, '2021-01-18', 361);
INSERT INTO daily_stats (id, date, profit) VALUES (19, '2021-01-19', 287);
INSERT INTO daily_stats (id, date, profit) VALUES (20, '2021-01-20', 320);
INSERT INTO daily_stats (id, date, profit) VALUES (21, '2021-01-21', 165);
INSERT INTO daily_stats (id, date, profit) VALUES (22, '2021-01-22', 482);
INSERT INTO daily_stats (id, date, profit) VALUES (23, '2021-01-23', 142);
INSERT INTO daily_stats (id, date, profit) VALUES (24, '2021-01-24', 472);
INSERT INTO daily_stats (id, date, profit) VALUES (25, '2021-01-25', 259);
INSERT INTO daily_stats (id, date, profit) VALUES (26, '2021-01-26', 325);
INSERT INTO daily_stats (id, date, profit) VALUES (27, '2021-01-27', 468);
INSERT INTO daily_stats (id, date, profit) VALUES (28, '2021-01-28', 488);
INSERT INTO daily_stats (id, date, profit) VALUES (29, '2021-01-29', 480);
INSERT INTO daily_stats (id, date, profit) VALUES (30, '2021-01-30', 332);
INSERT INTO daily_stats (id, date, profit) VALUES (31, '2021-01-31', 461);
INSERT INTO daily_stats (id, date, profit) VALUES (32, '2021-02-01', 248);
INSERT INTO daily_stats (id, date, profit) VALUES (33, '2021-02-02', 476);
INSERT INTO daily_stats (id, date, profit) VALUES (34, '2021-02-03', 339);
INSERT INTO daily_stats (id, date, profit) VALUES (35, '2021-02-04', 464);
INSERT INTO daily_stats (id, date, profit) VALUES (36, '2021-02-05', 156);
INSERT INTO daily_stats (id, date, profit) VALUES (37, '2021-02-06', 199);
INSERT INTO daily_stats (id, date, profit) VALUES (38, '2021-02-07', 359);
INSERT INTO daily_stats (id, date, profit) VALUES (39, '2021-02-08', 238);
INSERT INTO daily_stats (id, date, profit) VALUES (40, '2021-02-09', 412);
INSERT INTO daily_stats (id, date, profit) VALUES (41, '2021-02-10', 303);
INSERT INTO daily_stats (id, date, profit) VALUES (42, '2021-02-11', 406);
INSERT INTO daily_stats (id, date, profit) VALUES (43, '2021-02-12', 470);
INSERT INTO daily_stats (id, date, profit) VALUES (44, '2021-02-13', 404);
INSERT INTO daily_stats (id, date, profit) VALUES (45, '2021-02-14', 432);
INSERT INTO daily_stats (id, date, profit) VALUES (46, '2021-02-15', 347);
INSERT INTO daily_stats (id, date, profit) VALUES (47, '2021-02-16', 296);
INSERT INTO daily_stats (id, date, profit) VALUES (48, '2021-02-17', 431);
INSERT INTO daily_stats (id, date, profit) VALUES (49, '2021-02-18', 258);
INSERT INTO daily_stats (id, date, profit) VALUES (50, '2021-02-19', 217);
INSERT INTO daily_stats (id, date, profit) VALUES (51, '2021-02-20', 445);
INSERT INTO daily_stats (id, date, profit) VALUES (52, '2021-02-21', 372);
INSERT INTO daily_stats (id, date, profit) VALUES (53, '2021-02-22', 492);
INSERT INTO daily_stats (id, date, profit) VALUES (54, '2021-02-23', 156);
INSERT INTO daily_stats (id, date, profit) VALUES (55, '2021-02-24', 396);
INSERT INTO daily_stats (id, date, profit) VALUES (56, '2021-02-25', 220);
INSERT INTO daily_stats (id, date, profit) VALUES (57, '2021-02-26', 477);
INSERT INTO daily_stats (id, date, profit) VALUES (58, '2021-02-27', 116);
INSERT INTO daily_stats (id, date, profit) VALUES (59, '2021-02-28', 355);
INSERT INTO daily_stats (id, date, profit) VALUES (60, '2021-03-01', 338);
INSERT INTO daily_stats (id, date, profit) VALUES (61, '2021-03-02', 257);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select 
	day,
    MAX(total_profit) as total_profit,
	MAX(total_investment) as total_investment,
	MAX(user_investment) as user_investment,
	SUM(investment_share) as investment_share,
	ROUND(MAX(user_investment) / MAX(total_investment), 4) as user_share,
	MAX(total_profit) * SUM(investment_share) * (MAX(user_investment) / MAX(total_investment)) as user_profit
from 
(
	select
		s.date as day,
		SUM(i.amount) over (partition by s.date) as user_investment,
		SUM(s.profit) as total_profit,
		i.amount / (SUM(i.amount) over (partition by s.date)) * (i.percent / 100) as investment_share,
		(SUM(s.profit) * (MAX(i.percent) / 100 ) * i.amount / SUM(i.amount) OVER (PARTITION BY s.date)) AS user_profit
	from
		daily_stats as s
	join
		investments as i
	on
		s.date BETWEEN i.start_date AND i.end_date
	where
		s.date BETWEEN '2021-01-01'::date AND '2021-03-01'::date and user_id IN (1,4)
	group by s.date, i.amount, i.percent
	order by s.date
) as investment_stats
join 
(	
	select
		s.date as day,
		SUM(i.amount) as total_investment
	from
		daily_stats as s
	join
		investments as i
	on
		s.date BETWEEN i.start_date AND i.end_date
	where
		s.date BETWEEN '2021-01-01'::date AND '2021-03-01'::date
	group by s.date
	order by s.date
) as investment_totals
using(day)
group by day
;
         


day
total_profit
total_investment
user_investment
investment_share
user_share
user_profit


2021-01-01
254
10000
10000
0
1.0000
0

2021-01-02
188
10000
10000
0
1.0000
0

2021-01-03
361
10000
10000
0
1.0000
0

2021-01-04
469
10000
10000
0
1.0000
0

2021-01-05
194
10000
10000
0
1.0000
0

2021-01-06
155
10000
10000
0
1.0000
0

2021-01-07
322
10000
10000
0
1.0000
0

2021-01-08
354
10000
10000
0
1.0000
0

2021-01-09
409
10000
10000
0
1.0000
0

2021-01-10
132
10000
10000
0
1.0000
0

2021-01-11
172
10000
10000
0
1.0000
0

2021-01-12
176
10000
10000
0
1.0000
0

2021-01-13
269
10000
10000
0
1.0000
0

2021-01-14
195
10000
10000
0
1.0000
0

2021-01-15
152
30000
10000
0
0.0000
0

2021-01-16
460
30000
10000
0
0.0000
0

2021-01-17
423
30000
10000
0
0.0000
0

2021-01-18
361
30000
10000
0
0.0000
0

2021-01-19
287
30000
10000
0
0.0000
0

2021-01-20
320
30000
10000
0
0.0000
0

2021-01-21
165
30000
10000
0
0.0000
0

2021-01-22
482
30000
10000
0
0.0000
0

2021-01-23
142
30000
10000
0
0.0000
0

2021-01-24
472
30000
10000
0
0.0000
0

2021-01-25
259
36000
16000
0
0.0000
0

2021-01-26
325
36000
16000
0
0.0000
0

2021-01-27
468
36000
16000
0
0.0000
0

2021-01-28
488
36000
16000
0
0.0000
0

2021-01-29
480
36000
16000
0
0.0000
0

2021-01-30
332
36000
16000
0
0.0000
0

2021-01-31
461
36000
16000
0
0.0000
0

2021-02-01
248
31000
11000
0
0.0000
0

2021-02-02
476
31000
11000
0
0.0000
0

2021-02-03
339
31000
11000
0
0.0000
0

2021-02-04
464
31000
11000
0
0.0000
0

2021-02-05
156
31000
11000
0
0.0000
0

2021-02-06
199
31000
11000
0
0.0000
0

2021-02-07
359
31000
11000
0
0.0000
0

2021-02-08
238
31000
11000
0
0.0000
0

2021-02-09
412
31000
11000
0
0.0000
0

2021-02-10
303
61000
11000
0
0.0000
0

2021-02-11
406
61000
11000
0
0.0000
0

2021-02-12
470
61000
11000
0
0.0000
0

2021-02-13
404
61000
11000
0
0.0000
0

2021-02-14
432
61000
11000
0
0.0000
0

2021-02-15
347
61000
11000
0
0.0000
0

2021-02-16
296
41000
11000
0
0.0000
0

2021-02-17
431
41000
11000
0
0.0000
0

2021-02-18
258
41000
11000
0
0.0000
0

2021-02-19
217
41000
11000
0
0.0000
0

2021-02-20
445
41000
11000
0
0.0000
0

2021-02-21
372
41000
11000
0
0.0000
0

2021-02-22
492
41000
11000
0
0.0000
0

2021-02-23
156
41000
11000
0
0.0000
0

2021-02-24
396
41000
11000
0
0.0000
0

2021-02-25
220
41000
11000
0
0.0000
0

2021-02-26
477
35000
5000
0
0.0000
0

2021-02-27
116
35000
5000
0
0.0000
0

2021-02-28
355
35000
5000
0
0.0000
0


-- user_share (i.amount / (SUM(i.amount) over (partition by s.date))) * i.percent

  SELECT s.date, s.profit
       , i.user_id, i.amount, i.percent
       , SUM(i.amount) OVER (PARTITION BY s.date) AS total_inv
       , ROUND(s.profit * (i.percent / 100.0) * i.amount / SUM(i.amount) OVER (PARTITION BY s.date), 2) AS user_profit
       , ROUND((1.0 * i.amount / (SUM(i.amount) over (partition by s.date))) * i.percent, 2) AS user_share
    FROM daily_stats AS s
    JOIN investments AS i
      ON s.date BETWEEN i.start_date AND i.end_date
   WHERE s.date BETWEEN '2021-02-01' AND '2021-02-05'
;


date
profit
user_id
amount
percent
total_inv
user_profit
user_share


2021-02-01
248
1
5000
20
31000
8.00
3.23

2021-02-01
248
4
6000
45
31000
21.60
8.71

2021-02-01
248
2
20000
40
31000
64.00
25.81

2021-02-02
476
2
20000
40
31000
122.84
25.81

2021-02-02
476
1
5000
20
31000
15.35
3.23

2021-02-02
476
4
6000
45
31000
41.46
8.71

2021-02-03
339
2
20000
40
31000
87.48
25.81

2021-02-03
339
1
5000
20
31000
10.94
3.23

2021-02-03
339
4
6000
45
31000
29.53
8.71

2021-02-04
464
1
5000
20
31000
14.97
3.23

2021-02-04
464
4
6000
45
31000
40.41
8.71

2021-02-04
464
2
20000
40
31000
119.74
25.81

2021-02-05
156
2
20000
40
31000
40.26
25.81

2021-02-05
156
1
5000
20
31000
5.03
3.23

2021-02-05
156
4
6000
45
31000
13.59
8.71


WITH query1 AS (
  SELECT s.date, s.profit
       , i.user_id, i.amount, i.percent
       , SUM(i.amount) OVER (PARTITION BY s.date) AS total_inv
       , ROUND(s.profit * (i.percent / 100.0) * i.amount / SUM(i.amount) OVER (PARTITION BY s.date), 2) AS user_profit
       , ROUND((1.0 * i.amount / (SUM(i.amount) over (partition by s.date))) * i.percent, 2) AS user_share
    FROM daily_stats AS s
    JOIN investments AS i
      ON s.date BETWEEN i.start_date AND i.end_date
   WHERE s.date BETWEEN '2021-02-01' AND '2021-02-05'
     )
SELECT date
     , MAX(profit) AS profit
     , MAX(total_inv) AS total_inv
     , SUM(user_profit) AS total_profit
     , SUM(user_share) AS total_share
  FROM query1
 WHERE user_id IN (1, 4)
 GROUP BY date
;


date
profit
total_inv
total_profit
total_share


2021-02-01
248
31000
29.60
11.94

2021-02-02
476
31000
56.81
11.94

2021-02-03
339
31000
40.47
11.94

2021-02-04
464
31000
55.38
11.94

2021-02-05
156
31000
18.62
11.94

day	total_profit	total_investment	user_investment	user_share
2021-01-01	254	10000	10000	1.0000
2021-01-02	188	10000	10000	1.0000
2021-01-03	361	10000	10000	1.0000
2021-01-04	469	10000	10000	1.0000
2021-01-05	194	10000	10000	1.0000
2021-01-06	155	10000	10000	1.0000
2021-01-07	322	10000	10000	1.0000
2021-01-08	354	10000	10000	1.0000
2021-01-09	409	10000	10000	1.0000
2021-01-10	132	10000	10000	1.0000
2021-01-11	172	10000	10000	1.0000
2021-01-12	176	10000	10000	1.0000
2021-01-13	269	10000	10000	1.0000
2021-01-14	195	10000	10000	1.0000
2021-01-15	152	30000	10000	0.0000
2021-01-16	460	30000	10000	0.0000
2021-01-17	423	30000	10000	0.0000
2021-01-18	361	30000	10000	0.0000
2021-01-19	287	30000	10000	0.0000
2021-01-20	320	30000	10000	0.0000
2021-01-21	165	30000	10000	0.0000
2021-01-22	482	30000	10000	0.0000
2021-01-23	142	30000	10000	0.0000
2021-01-24	472	30000	10000	0.0000
2021-01-25	259	36000	16000	0.0000
2021-01-26	325	36000	16000	0.0000
2021-01-27	468	36000	16000	0.0000
2021-01-28	488	36000	16000	0.0000
2021-01-29	480	36000	16000	0.0000
2021-01-30	332	36000	16000	0.0000
2021-01-31	461	36000	16000	0.0000
2021-02-01	248	31000	11000	0.0000
2021-02-02	476	31000	11000	0.0000
2021-02-03	339	31000	11000	0.0000
2021-02-04	464	31000	11000	0.0000
2021-02-05	156	31000	11000	0.0000
2021-02-06	199	31000	11000	0.0000
2021-02-07	359	31000	11000	0.0000
2021-02-08	238	31000	11000	0.0000
2021-02-09	412	31000	11000	0.0000
2021-02-10	303	61000	11000	0.0000
2021-02-11	406	61000	11000	0.0000
2021-02-12	470	61000	11000	0.0000
2021-02-13	404	61000	11000	0.0000
2021-02-14	432	61000	11000	0.0000
2021-02-15	347	61000	11000	0.0000
2021-02-16	296	41000	11000	0.0000
2021-02-17	431	41000	11000	0.0000
2021-02-18	258	41000	11000	0.0000
2021-02-19	217	41000	11000	0.0000
2021-02-20	445	41000	11000	0.0000
2021-02-21	372	41000	11000	0.0000
2021-02-22	492	41000	11000	0.0000
2021-02-23	156	41000	11000	0.0000
2021-02-24	396	41000	11000	0.0000
2021-02-25	220	41000	11000	0.0000
2021-02-26	477	35000	5000	0.0000
2021-02-27	116	35000	5000	0.0000
2021-02-28	355	35000	5000	0.0000

date	profit	total_inv	total_profit	total_share
2021-02-01	248	31000	29.60	11.94
2021-02-02	476	31000	56.81	11.94
2021-02-03	339	31000	40.47	11.94
2021-02-04	464	31000	55.38	11.94
2021-02-05	156	31000	18.62	11.94