Postgres 12

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 payments
    ("id" int, "payment_type_id" int, "payment_date" date, "amount" int)
;
    
INSERT INTO payments
    ("id", "payment_type_id", "payment_date", "amount")
VALUES
    (4, 1, '2019-11-18', 300.00),
    (3, 1, '2019-11-17', 1000.00),
    (2, 1, '2019-11-16', 250.00),
    (1, 1, '2019-11-15', 300.00),
    
    (5, 1, '2019-11-14', 300.00), -- << inserted for test, should be for october
    
    (14, 1, '2019-10-18', 130.00),
    (13, 1, '2019-10-18', 100.00),
    (15, 1, '2019-09-18', 1300.00),
    (16, 1, '2019-09-17', 1300.00),
    (17, 1, '2019-09-01', 400.00),
    (18, 1, '2019-08-25', 400.00)
;
11 rows affected

SELECT 
    amount
    payment_date,
    CASE 
        WHEN date_part('day', payment_date) < 15 THEN 
            date_trunc('month', payment_date) + interval '-1month 14 days'
        ELSE  date_trunc('month', payment_date) + interval '14 days'
    END AS payment_date
FROM
    payments


payment_date
payment_date


300
2019-11-15 00:00:00+00

1000
2019-11-15 00:00:00+00

250
2019-11-15 00:00:00+00

300
2019-11-15 00:00:00+00

300
2019-10-15 00:00:00+01

130
2019-10-15 00:00:00+01

100
2019-10-15 00:00:00+01

1300
2019-09-15 00:00:00+01

1300
2019-09-15 00:00:00+01

400
2019-08-15 00:00:00+01

400
2019-08-15 00:00:00+01


select payment_type_id, date_trunc('month', payment_date - interval '14 day') + interval '14 day' as month_15,
       sum(amount)
from payments
group by payment_type_id, month_15
order by payment_type_id, month_15;


payment_type_id
month_15
sum


1
2019-08-15 00:00:00
800

1
2019-09-15 00:00:00
2600

1
2019-10-15 00:00:00
530

1
2019-11-15 00:00:00
1850

payment_date	payment_date
300	2019-11-15 00:00:00+00
1000	2019-11-15 00:00:00+00
250	2019-11-15 00:00:00+00
300	2019-11-15 00:00:00+00
300	2019-10-15 00:00:00+01
130	2019-10-15 00:00:00+01
100	2019-10-15 00:00:00+01
1300	2019-09-15 00:00:00+01
1300	2019-09-15 00:00:00+01
400	2019-08-15 00:00:00+01
400	2019-08-15 00:00:00+01

payment_type_id	month_15	sum
1	2019-08-15 00:00:00	800
1	2019-09-15 00:00:00	2600
1	2019-10-15 00:00:00	530
1	2019-11-15 00:00:00	1850