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?.
WITH dates AS (
SELECT to_date('2023-01-01', 'YYYY-MM-DD') AS d
UNION ALL
SELECT to_date('2023-02-01', 'YYYY-MM-DD') AS d
UNION ALL
SELECT to_date('2023-03-01', 'YYYY-MM-DD') AS d
UNION ALL
SELECT to_date('2023-04-01', 'YYYY-MM-DD') AS d
UNION ALL
SELECT to_date('2023-05-01', 'YYYY-MM-DD') AS d
)
, product_sales as(
--Yanuary
SELECT 1 AS prod_id, 'test1' AS prod_name, to_date('2023-01-01', 'YYYY-MM-DD') AS sale_date, 3 AS sale_amount
UNION ALL
SELECT 2 AS prod_id, 'test2' AS prod_name, to_date('2023-01-01', 'YYYY-MM-DD') AS sale_date, 8 AS sale_amount
UNION ALL
SELECT 3 AS prod_id, 'test3' AS prod_name, to_date('2023-01-01', 'YYYY-MM-DD') AS sale_date, 9 AS sale_amount
UNION ALL
--February
SELECT 1 AS prod_id, 'test1' AS prod_name, to_date('2023-02-01', 'YYYY-MM-DD') AS sale_date, 5 AS sale_amount
UNION ALL
SELECT 2 AS prod_id, 'test2' AS prod_name, to_date('2023-02-01', 'YYYY-MM-DD') AS sale_date, 2 AS sale_amount
UNION ALL
SELECT 3 AS prod_id, 'test3' AS prod_name, to_date('2023-02-01', 'YYYY-MM-DD') AS sale_date, 1 AS sale_amount
UNION ALL
--March
-- SELECT 1 AS prod_id, 'test1' AS prod_name, to_date('2023-02-01', 'YYYY-MM-DD') AS sale_date, 5 AS sale_amount
-- UNION ALL
SELECT 2 AS prod_id, 'test2' AS prod_name, to_date('2023-03-01', 'YYYY-MM-DD') AS sale_date, 7 AS sale_amount
-- UNION ALL
-- SELECT 3 AS prod_id, 'test3' AS prod_name, to_date('2023-02-01', 'YYYY-MM-DD') AS sale_date, 1 AS sale_amount
| prod_id | prod_name | d | month_sale_amount |
|---|---|---|---|
| 1 | test1 | 2023-01-01 | 3 |
| 2 | test2 | 2023-01-01 | 8 |
| 3 | test3 | 2023-01-01 | 9 |
| 1 | test1 | 2023-02-01 | 5 |
| 2 | test2 | 2023-02-01 | 2 |
| 3 | test3 | 2023-02-01 | 1 |
| 1 | test1 | 2023-03-01 | 0 |
| 2 | test2 | 2023-03-01 | 7 |
| 3 | test3 | 2023-03-01 | 0 |
| 1 | test1 | 2023-04-01 | 1 |
| 2 | test2 | 2023-04-01 | 2 |
| 3 | test3 | 2023-04-01 | 0 |
| 1 | test1 | 2023-05-01 | 6 |
| 2 | test2 | 2023-05-01 | 4 |
| 3 | test3 | 2023-05-01 | 2 |
SELECT 15