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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
create table x( xdate date
, xgroup text
, xdays integer
);
insert into x (xdate, xgroup, xdays)
values ('2023-01-01', 'A', 3)
, ('2023-01-05', 'B', 4);
select * from x;
CREATE TABLE
INSERT 0 2
| xdate | xgroup | xdays |
|---|---|---|
| 2023-01-01 | A | 3 |
| 2023-01-05 | B | 4 |
SELECT 2
select (generate_series( xdate
, xdate + (xdays-1) * interval '1 day'
, interval '1 day'
)
)::date "Date"
, xgroup "Group"
, xdays "Days"
from x;
| Date | Group | Days |
|---|---|---|
| 2023-01-01 | A | 3 |
| 2023-01-02 | A | 3 |
| 2023-01-03 | A | 3 |
| 2023-01-05 | B | 4 |
| 2023-01-06 | B | 4 |
| 2023-01-07 | B | 4 |
| 2023-01-08 | B | 4 |
SELECT 7