Postgres 15

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 budgets (
  id       int PRIMARY KEY
, rate     numeric NOT NULL
, timespan daterange NOT NULL  -- type simplified!
);

INSERT INTO budgets VALUES
  (1, 10, '[2023-07-19,)')
, (2, 10, '[2023-07-19, 2023-09-05)')
;

CREATE TABLE



INSERT 0 2


-- assming today = '2023-09-05'

SELECT mon_start::date + i AS the_day, round(rate/mon_days, 3) AS daily_rate
FROM   budgets b
CROSS  JOIN LATERAL (
   SELECT *,  LEAST(mon_start + interval '1 month', '2023-09-05')::date - mon_start::date AS mon_days
   FROM   generate_series (lower(timespan)::timestamp
                         , LEAST(upper(timespan)::timestamp, '2023-09-05')  -- LOCALTIMESTAMP
                         , interval '1 month') mon_start
   ) m
     , generate_series (0, m.mon_days - 1) i
WHERE  id = 1;


the_day
daily_rate


2023-07-19
0.323

2023-07-20
0.323

2023-07-21
0.323

2023-07-22
0.323

2023-07-23
0.323

2023-07-24
0.323

2023-07-25
0.323

2023-07-26
0.323

2023-07-27
0.323

2023-07-28
0.323

2023-07-29
0.323

2023-07-30
0.323

2023-07-31
0.323

2023-08-01
0.323

2023-08-02
0.323

2023-08-03
0.323

2023-08-04
0.323

2023-08-05
0.323

2023-08-06
0.323

2023-08-07
0.323

2023-08-08
0.323

2023-08-09
0.323

2023-08-10
0.323

2023-08-11
0.323

2023-08-12
0.323

2023-08-13
0.323

2023-08-14
0.323

2023-08-15
0.323

2023-08-16
0.323

2023-08-17
0.323

2023-08-18
0.323

2023-08-19
0.588

2023-08-20
0.588

2023-08-21
0.588

2023-08-22
0.588

2023-08-23
0.588

2023-08-24
0.588

2023-08-25
0.588

2023-08-26
0.588

2023-08-27
0.588

2023-08-28
0.588

2023-08-29
0.588

2023-08-30
0.588

2023-08-31
0.588

2023-09-01
0.588

2023-09-02
0.588

2023-09-03
0.588

2023-09-04
0.588



SELECT 48


-- Use LOCALTIMESTAMP as "today"
-- but with a range that ends '2023-09-05'
-- and with intermediate steps

SELECT mon_start::date + i AS the_day, round(rate/mon_days, 3) AS daily_rate, *
FROM   budgets b
CROSS  JOIN LATERAL (
   SELECT *,  LEAST(mon_start + interval '1 month', LOCALTIMESTAMP)::date - mon_start::date AS mon_days
   FROM   generate_series (lower(timespan)::timestamp
                         , LEAST(upper(timespan)::timestamp, LOCALTIMESTAMP)
                         , interval '1 month') mon_start
   ) m
     , generate_series (0, m.mon_days - 1) i
WHERE  id = 2;


the_day
daily_rate
id
rate
timespan
mon_start
mon_days
i


2023-07-19
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
0

2023-07-20
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
1

2023-07-21
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
2

2023-07-22
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
3

2023-07-23
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
4

2023-07-24
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
5

2023-07-25
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
6

2023-07-26
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
7

2023-07-27
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
8

2023-07-28
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
9

2023-07-29
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
10

2023-07-30
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
11

2023-07-31
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
12

2023-08-01
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
13

2023-08-02
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
14

2023-08-03
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
15

2023-08-04
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
16

2023-08-05
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
17

2023-08-06
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
18

2023-08-07
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
19

2023-08-08
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
20

2023-08-09
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
21

2023-08-10
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
22

2023-08-11
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
23

2023-08-12
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
24

2023-08-13
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
25

2023-08-14
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
26

2023-08-15
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
27

2023-08-16
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
28

2023-08-17
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
29

2023-08-18
0.323
2
10
[2023-07-19,2023-09-05)
2023-07-19 00:00:00
31
30

2023-08-19
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
0

2023-08-20
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
1

2023-08-21
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
2

2023-08-22
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
3

2023-08-23
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
4

2023-08-24
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
5

2023-08-25
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
6

2023-08-26
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
7

2023-08-27
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
8

2023-08-28
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
9

2023-08-29
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
10

2023-08-30
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
11

2023-08-31
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
12

2023-09-01
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
13

2023-09-02
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
14

2023-09-03
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
15

2023-09-04
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
16

2023-09-05
0.556
2
10
[2023-07-19,2023-09-05)
2023-08-19 00:00:00
18
17



SELECT 49

the_day	daily_rate
2023-07-19	0.323
2023-07-20	0.323
2023-07-21	0.323
2023-07-22	0.323
2023-07-23	0.323
2023-07-24	0.323
2023-07-25	0.323
2023-07-26	0.323
2023-07-27	0.323
2023-07-28	0.323
2023-07-29	0.323
2023-07-30	0.323
2023-07-31	0.323
2023-08-01	0.323
2023-08-02	0.323
2023-08-03	0.323
2023-08-04	0.323
2023-08-05	0.323
2023-08-06	0.323
2023-08-07	0.323
2023-08-08	0.323
2023-08-09	0.323
2023-08-10	0.323
2023-08-11	0.323
2023-08-12	0.323
2023-08-13	0.323
2023-08-14	0.323
2023-08-15	0.323
2023-08-16	0.323
2023-08-17	0.323
2023-08-18	0.323
2023-08-19	0.588
2023-08-20	0.588
2023-08-21	0.588
2023-08-22	0.588
2023-08-23	0.588
2023-08-24	0.588
2023-08-25	0.588
2023-08-26	0.588
2023-08-27	0.588
2023-08-28	0.588
2023-08-29	0.588
2023-08-30	0.588
2023-08-31	0.588
2023-09-01	0.588
2023-09-02	0.588
2023-09-03	0.588
2023-09-04	0.588

the_day	daily_rate	id	rate	timespan	mon_start	mon_days	i
2023-07-19	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	0
2023-07-20	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	1
2023-07-21	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	2
2023-07-22	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	3
2023-07-23	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	4
2023-07-24	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	5
2023-07-25	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	6
2023-07-26	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	7
2023-07-27	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	8
2023-07-28	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	9
2023-07-29	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	10
2023-07-30	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	11
2023-07-31	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	12
2023-08-01	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	13
2023-08-02	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	14
2023-08-03	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	15
2023-08-04	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	16
2023-08-05	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	17
2023-08-06	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	18
2023-08-07	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	19
2023-08-08	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	20
2023-08-09	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	21
2023-08-10	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	22
2023-08-11	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	23
2023-08-12	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	24
2023-08-13	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	25
2023-08-14	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	26
2023-08-15	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	27
2023-08-16	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	28
2023-08-17	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	29
2023-08-18	0.323	2	10	[2023-07-19,2023-09-05)	2023-07-19 00:00:00	31	30
2023-08-19	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	0
2023-08-20	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	1
2023-08-21	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	2
2023-08-22	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	3
2023-08-23	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	4
2023-08-24	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	5
2023-08-25	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	6
2023-08-26	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	7
2023-08-27	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	8
2023-08-28	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	9
2023-08-29	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	10
2023-08-30	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	11
2023-08-31	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	12
2023-09-01	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	13
2023-09-02	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	14
2023-09-03	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	15
2023-09-04	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	16
2023-09-05	0.556	2	10	[2023-07-19,2023-09-05)	2023-08-19 00:00:00	18	17