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,
    country VARCHAR(255),
    sales_date DATE,
    sales_volume DECIMAL,
    fix_costs DECIMAL
);

INSERT INTO sales
(country, sales_date, sales_volume, fix_costs
)
VALUES 

('DE', '2020-01-03', '500', '0'),
('FR', '2020-01-03', '350', '0'),
('None', '2020-01-31', '0', '2000'),

('DE', '2020-02-15', '0', '0'),
('FR', '2020-02-15', '0', '0'),
('None', '2020-02-29', '0', '5000'),

('DE', '2020-03-27', '180', '0'),
('FR', '2020-03-27', '970', '0'),
('None', '2020-03-31', '0', '4000');
9 rows affected

SELECT
    s1.sales_date,
    s1.country,
    s1.sales_volume,
    s2.fix_costs
FROM sales s1
JOIN sales s2 ON s1.country <> 'None' AND s2.country = 'None' 
    AND date_trunc('month', s1.sales_date) = date_trunc('month', s2.sales_date)


sales_date
country
sales_volume
fix_costs


2020-01-03
DE
500
2000

2020-01-03
FR
350
2000

2020-02-15
DE
0
5000

2020-02-15
FR
0
5000

2020-03-27
DE
180
4000

2020-03-27
FR
970
4000


SELECT
    *
FROM (
    SELECT
        sales_date,
        country,
        sales_volume,
        sum(fix_costs) OVER (PARTITION BY date_trunc('month', sales_date)) as fix_costs
    FROM sales 
) s
WHERE country <> 'None'


sales_date
country
sales_volume
fix_costs


2020-01-03
DE
500
2000

2020-01-03
FR
350
2000

2020-02-15
DE
0
5000

2020-02-15
FR
0
5000

2020-03-27
DE
180
4000

2020-03-27
FR
970
4000

sales_date	country	sales_volume	fix_costs
2020-01-03	DE	500	2000
2020-01-03	FR	350	2000
2020-02-15	DE	0	5000
2020-02-15	FR	0	5000
2020-03-27	DE	180	4000
2020-03-27	FR	970	4000