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?.
select version();
| version |
|---|
| PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
create table mytable as
SELECT '01.01.2024'::date AS data, 67 AS cur UNION ALL
SELECT '03.01.2024'::date AS data, 68 AS cur UNION ALL
SELECT '04.01.2024'::date AS data, 69 AS cur UNION ALL
SELECT '06.01.2024'::date AS data, 70 AS cur UNION ALL
SELECT '04.02.2024'::date AS data, 71 AS cur UNION ALL
SELECT '07.02.2024'::date AS data, 72 AS cur UNION ALL
SELECT '06.03.2024'::date AS data, 77 AS cur UNION ALL
SELECT '04.05.2024'::date AS data, 78 AS cur UNION ALL
SELECT '03.06.2024'::date AS data, 79 AS cur UNION ALL
SELECT '04.06.2024'::date AS data, 80 AS cur UNION ALL
SELECT '05.06.2024'::date AS data, 81 AS cur;
SELECT 11
WITH dates (dt) AS
(SELECT generate_series::DATE AS dt FROM generate_series(DATE '2024-01-01', DATE '2024-12-01', '1 month'))
SELECT d.dt, t.cur
FROM dates d
LEFT JOIN LATERAL
(
SELECT *
FROM mytable mt
WHERE mt.data <= d.dt
ORDER BY mt.data DESC
FETCH FIRST ROW ONLY
) t ON TRUE
ORDER BY d.dt;
| dt | cur |
|---|---|
| 2024-01-01 | 67 |
| 2024-02-01 | 70 |
| 2024-03-01 | 72 |
| 2024-04-01 | 77 |
| 2024-05-01 | 77 |
| 2024-06-01 | 78 |
| 2024-07-01 | 81 |
| 2024-08-01 | 81 |
| 2024-09-01 | 81 |
| 2024-10-01 | 81 |
| 2024-11-01 | 81 |
| 2024-12-01 | 81 |
SELECT 12