Postgres 13

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 sales (
    id SERIAL PRIMARY KEY,
    event_date DATE,
    sales_volume INT
);

INSERT INTO sales
(event_date, sales_volume)
VALUES 
('2023-01-27', '900'),
('2023-01-28', '500'),
('2023-01-29', '100'),
('2023-01-30', '600'),
('2023-01-31', '450'),
  
('2023-02-25', '300'),
('2023-02-26', '250'),
('2023-02-27', '845'),
('2023-02-28', '520'), 

('2023-03-26', '750'),
('2023-03-27', '750'),
('2023-03-28', '625'),
('2023-03-29', '885'),
('2023-03-30', '120'),
('2023-03-31', '400');



CREATE TABLE



INSERT 0 15


    SELECT
        row_number() OVER (
            PARTITION BY date_trunc('month', event_date) 
            ORDER BY event_date DESC
        ),
        SUM(sales_volume)
    FROM sales 
 
    GROUP BY date_trunc('month', event_date)

ERROR:  column "sales.event_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4:             ORDER BY event_date DESC
                             ^


SELECT
    date_trunc('month', event_date) as month,
    SUM(sales_volume)
FROM (
    SELECT
        *,
        row_number() OVER (
            PARTITION BY date_trunc('month', event_date) 
            ORDER BY event_date DESC
        )
    FROM sales 
) s
WHERE row_number <= 3
GROUP BY 1





month
sum


2023-01-01 00:00:00+00
1150

2023-02-01 00:00:00+00
1615

2023-03-01 00:00:00+00
1405



SELECT 3

month	sum
2023-01-01 00:00:00+00	1150
2023-02-01 00:00:00+00	1615
2023-03-01 00:00:00+00	1405