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 IF NOT EXISTS item_ (
id SERIAL PRIMARY KEY,
transaction_date DATE NOT NULL,
cost NUMERIC NOT NULL)
CREATE TABLE
INSERT INTO item_ VALUES
(1,'2019-01-01',94.12)
,(2,'2019-02-15',95.01)
,(3,'2020-05-16',45.22)
,(4,'2020-06-10',10.84)
,(5,'2020-03-01',36.22)
,(6,'2020-12-09',12.42)
,(7,'2021-05-16',64.88)
,(8,'2021-02-28',72.44)
,(9,'2021-04-16',91.08)
,(10,'2022-06-09',53.34)
,(11,'2022-10-05',36.93)
,(12,'2022-01-22',55.87)
,(13,'2022-04-06',99.66)
,(14,'2022-11-18',90.04)
,(15,'2023-01-23',82.30)
INSERT 0 15
select * from item_ order by transaction_date asc;
| id | transaction_date | cost |
|---|---|---|
| 1 | 2019-01-01 | 94.12 |
| 2 | 2019-02-15 | 95.01 |
| 5 | 2020-03-01 | 36.22 |
| 3 | 2020-05-16 | 45.22 |
| 4 | 2020-06-10 | 10.84 |
| 6 | 2020-12-09 | 12.42 |
| 8 | 2021-02-28 | 72.44 |
| 9 | 2021-04-16 | 91.08 |
| 7 | 2021-05-16 | 64.88 |
| 12 | 2022-01-22 | 55.87 |
| 13 | 2022-04-06 | 99.66 |
| 10 | 2022-06-09 | 53.34 |
| 11 | 2022-10-05 | 36.93 |
| 14 | 2022-11-18 | 90.04 |
| 15 | 2023-01-23 | 82.30 |
SELECT 15
select EXTRACT(YEAR FROM item_.transaction_date) AS year,
sum(case when EXTRACT(YEAR FROM CURRENT_DATE)>EXTRACT(YEAR FROM item_.transaction_date)
then item_.cost
else 0 end)
as box
from item_
GROUP BY year;
| year | box |
|---|---|
| 2022 | 335.84 |
| 2019 | 189.13 |
| 2020 | 104.70 |
| 2021 | 228.40 |
| 2023 | 0 |
SELECT 5
CREATE TABLE IF NOT EXISTS results (
year integer,
box NUMERIC)
CREATE TABLE
INSERT INTO results VALUES
(2019,189.13)
,(2020,104.70)
,(2021,228.40)
,(2023,335.84)
,(2023,503.76)
INSERT 0 5
select * from results order by year asc;
| year | box |
|---|---|
| 2019 | 189.13 |
| 2020 | 104.70 |
| 2021 | 228.40 |
| 2023 | 335.84 |
| 2023 | 503.76 |
SELECT 5