Postgres 16

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 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


CREATE TABLE orders (
    order_id VARCHAR(50),
    order_completed_at DATE,
    order_amount INT,
    customer_id VARCHAR(50)
   
);

CREATE TABLE


INSERT INTO orders (order_id, order_completed_at, order_amount, customer_id)
VALUES
('ord_1', '2024-05-01', 1, 'aad_1'),
('ord_2', '2024-05-01', 5, 'aad_1'),
('ord_3', '2024-05-05', 10, 'aad_1'),
('ord_4', '2024-05-15', 15, 'aad_1');

INSERT 0 4


select * from orders;


order_id
order_completed_at
order_amount
customer_id


ord_1
2024-05-01
1
aad_1

ord_2
2024-05-01
5
aad_1

ord_3
2024-05-05
10
aad_1

ord_4
2024-05-15
15
aad_1



SELECT 4


WITH rolling_sum AS (
    SELECT 
        o1.order_id,
        o1.order_completed_at,
        o1.order_amount,
        o1.customer_id,
        SUM(o2.order_amount) AS sum_10_days
    FROM orders o1
    JOIN orders o2
        ON o1.customer_id = o2.customer_id
        AND o2.order_completed_at >= o1.order_completed_at - INTERVAL '10 days'
        AND o2.order_completed_at <= o1.order_completed_at
    GROUP BY 
        o1.order_id,
        o1.order_completed_at,
        o1.order_amount,
        o1.customer_id
)
SELECT * FROM rolling_sum
  ORDER BY order_id;


order_id
order_completed_at
order_amount
customer_id
sum_10_days


ord_1
2024-05-01
1
aad_1
6

ord_2
2024-05-01
5
aad_1
6

ord_3
2024-05-05
10
aad_1
16

ord_4
2024-05-15
15
aad_1
25



SELECT 4


CREATE TABLE orders1 (
    order_id VARCHAR(50),
    order_completed_at DATE,
    order_amount INT,
    customer_id VARCHAR(50)
   
);

CREATE TABLE


-- Original data + additional orders
INSERT INTO orders1 (order_id, order_completed_at, order_amount, customer_id)
VALUES
('ord_1', '2024-05-01', 1, 'aad_1'),
('ord_2', '2024-05-01', 5, 'aad_1'),
('ord_3', '2024-05-05', 10, 'aad_1'),
('ord_4', '2024-05-15', 15, 'aad_1'),
('ord_5', '2024-06-01', 8, 'aad_1'),
('ord_6', '2024-06-05', 12, 'aad_1'),
('ord_7', '2024-06-15', 20, 'aad_1'),
('ord_8', '2024-06-20', 30, 'aad_1'),
('ord_9', '2024-06-25', 40, 'aad_1');

INSERT 0 9


select * from orders1;


order_id
order_completed_at
order_amount
customer_id


ord_1
2024-05-01
1
aad_1

ord_2
2024-05-01
5
aad_1

ord_3
2024-05-05
10
aad_1

ord_4
2024-05-15
15
aad_1

ord_5
2024-06-01
8
aad_1

ord_6
2024-06-05
12
aad_1

ord_7
2024-06-15
20
aad_1

ord_8
2024-06-20
30
aad_1

ord_9
2024-06-25
40
aad_1



SELECT 9


WITH rolling_sum AS (
    SELECT 
        o1.order_id,
        o1.order_completed_at,
        o1.order_amount,
        o1.customer_id,
        
        SUM(o2.order_amount) AS sum_10_days
    FROM orders1 o1
    JOIN orders1 o2
        ON o1.customer_id = o2.customer_id
        AND o2.order_completed_at >= o1.order_completed_at - INTERVAL '10 days'
        AND o2.order_completed_at <= o1.order_completed_at
    GROUP BY 
        o1.order_id,
        o1.order_completed_at,
        o1.order_amount,
        o1.customer_id
        
)
SELECT * FROM rolling_sum ORDER BY order_completed_at;


order_id
order_completed_at
order_amount
customer_id
sum_10_days


ord_2
2024-05-01
5
aad_1
6

ord_1
2024-05-01
1
aad_1
6

ord_3
2024-05-05
10
aad_1
16

ord_4
2024-05-15
15
aad_1
25

ord_5
2024-06-01
8
aad_1
8

ord_6
2024-06-05
12
aad_1
20

ord_7
2024-06-15
20
aad_1
32

ord_8
2024-06-20
30
aad_1
50

ord_9
2024-06-25
40
aad_1
90



SELECT 9
order_id	order_completed_at	order_amount	customer_id
ord_1	2024-05-01	1	aad_1
ord_2	2024-05-01	5	aad_1
ord_3	2024-05-05	10	aad_1
ord_4	2024-05-15	15	aad_1