Postgres 14

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

begin;
create table order_history(id serial,pickup_date date,   return_date date);
insert into order_history(pickup_date,return_date) values('2020-03-01', '2020-03-12'),
('2020-03-01', '2020-03-22'),
('2020-03-11', '2020-03-22'),
('2020-02-11', '2020-03-22'),
('2020-01-01', '2020-01-22'),
('2020-01-01', '2020-04-22');
commit;

SELECT * FROM order_history; 
6 rows affected



id
pickup_date
return_date


1
2020-03-01
2020-03-12

2
2020-03-01
2020-03-22

3
2020-03-11
2020-03-22

4
2020-02-11
2020-03-22

5
2020-01-01
2020-01-22

6
2020-01-01
2020-04-22


SELECT
    *,
    gs
FROM order_history oh,
    generate_series(date_trunc('month', pickup_date), date_trunc('month', return_date), interval '1 month') gs


id
pickup_date
return_date
gs
gs


1
2020-03-01
2020-03-12
2020-03-01 00:00:00+00
2020-03-01 00:00:00+00

2
2020-03-01
2020-03-22
2020-03-01 00:00:00+00
2020-03-01 00:00:00+00

3
2020-03-11
2020-03-22
2020-03-01 00:00:00+00
2020-03-01 00:00:00+00

4
2020-02-11
2020-03-22
2020-02-01 00:00:00+00
2020-02-01 00:00:00+00

4
2020-02-11
2020-03-22
2020-03-01 00:00:00+00
2020-03-01 00:00:00+00

5
2020-01-01
2020-01-22
2020-01-01 00:00:00+00
2020-01-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-01-01 00:00:00+00
2020-01-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-02-01 00:00:00+00
2020-02-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-03-01 00:00:00+00
2020-03-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-04-01 00:00:00+01
2020-04-01 00:00:00+01


SELECT
    *,
    LEAST(return_date, gs + interval '1 month - 1 day'),
    GREATEST(pickup_date, gs)
FROM order_history oh,
    generate_series(date_trunc('month', pickup_date), date_trunc('month', return_date), interval '1 month') gs


id
pickup_date
return_date
gs
least
greatest


1
2020-03-01
2020-03-12
2020-03-01 00:00:00+00
2020-03-12 00:00:00+00
2020-03-01 00:00:00+00

2
2020-03-01
2020-03-22
2020-03-01 00:00:00+00
2020-03-22 00:00:00+00
2020-03-01 00:00:00+00

3
2020-03-11
2020-03-22
2020-03-01 00:00:00+00
2020-03-22 00:00:00+00
2020-03-11 00:00:00+00

4
2020-02-11
2020-03-22
2020-02-01 00:00:00+00
2020-02-29 00:00:00+00
2020-02-11 00:00:00+00

4
2020-02-11
2020-03-22
2020-03-01 00:00:00+00
2020-03-22 00:00:00+00
2020-03-01 00:00:00+00

5
2020-01-01
2020-01-22
2020-01-01 00:00:00+00
2020-01-22 00:00:00+00
2020-01-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-01-01 00:00:00+00
2020-01-31 00:00:00+00
2020-01-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-02-01 00:00:00+00
2020-02-29 00:00:00+00
2020-02-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-03-01 00:00:00+00
2020-03-31 00:00:00+01
2020-03-01 00:00:00+00

6
2020-01-01
2020-04-22
2020-04-01 00:00:00+01
2020-04-22 00:00:00+01
2020-04-01 00:00:00+01


SELECT
    *,
    LEAST(return_date, gs + interval '1 month - 1 day') -
    GREATEST(pickup_date, gs) + interval '1 day'
FROM order_history oh,
    generate_series(date_trunc('month', pickup_date), date_trunc('month', return_date), interval '1 month') gs


id
pickup_date
return_date
gs
?column?


1
2020-03-01
2020-03-12
2020-03-01 00:00:00+00
12 days

2
2020-03-01
2020-03-22
2020-03-01 00:00:00+00
22 days

3
2020-03-11
2020-03-22
2020-03-01 00:00:00+00
12 days

4
2020-02-11
2020-03-22
2020-02-01 00:00:00+00
19 days

4
2020-02-11
2020-03-22
2020-03-01 00:00:00+00
22 days

5
2020-01-01
2020-01-22
2020-01-01 00:00:00+00
22 days

6
2020-01-01
2020-04-22
2020-01-01 00:00:00+00
31 days

6
2020-01-01
2020-04-22
2020-02-01 00:00:00+00
29 days

6
2020-01-01
2020-04-22
2020-03-01 00:00:00+00
30 days 23:00:00

6
2020-01-01
2020-04-22
2020-04-01 00:00:00+01
22 days


SELECT
    id,
    ARRAY_AGG(
        LEAST(return_date, gs + interval '1 month - 1 day') -
        GREATEST(pickup_date, gs) + interval '1 day'
    )
FROM order_history oh,
    generate_series(date_trunc('month', pickup_date), date_trunc('month', return_date), interval '1 month') gs
GROUP BY id


id
array_agg


3
{"12 days"}

5
{"22 days"}

4
{"19 days","22 days"}

6
{"31 days","29 days","30 days 23:00:00","22 days"}

2
{"22 days"}

1
{"12 days"}

id	pickup_date	return_date
1	2020-03-01	2020-03-12
2	2020-03-01	2020-03-22
3	2020-03-11	2020-03-22
4	2020-02-11	2020-03-22
5	2020-01-01	2020-01-22
6	2020-01-01	2020-04-22

id	pickup_date	return_date	gs	gs
1	2020-03-01	2020-03-12	2020-03-01 00:00:00+00	2020-03-01 00:00:00+00
2	2020-03-01	2020-03-22	2020-03-01 00:00:00+00	2020-03-01 00:00:00+00
3	2020-03-11	2020-03-22	2020-03-01 00:00:00+00	2020-03-01 00:00:00+00
4	2020-02-11	2020-03-22	2020-02-01 00:00:00+00	2020-02-01 00:00:00+00
4	2020-02-11	2020-03-22	2020-03-01 00:00:00+00	2020-03-01 00:00:00+00
5	2020-01-01	2020-01-22	2020-01-01 00:00:00+00	2020-01-01 00:00:00+00
6	2020-01-01	2020-04-22	2020-01-01 00:00:00+00	2020-01-01 00:00:00+00
6	2020-01-01	2020-04-22	2020-02-01 00:00:00+00	2020-02-01 00:00:00+00
6	2020-01-01	2020-04-22	2020-03-01 00:00:00+00	2020-03-01 00:00:00+00
6	2020-01-01	2020-04-22	2020-04-01 00:00:00+01	2020-04-01 00:00:00+01

id	array_agg
3	{"12 days"}
5	{"22 days"}
4	{"19 days","22 days"}
6	{"31 days","29 days","30 days 23:00:00","22 days"}
2	{"22 days"}
1	{"12 days"}