Postgres 15

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 cl_income(income_id Serial not null, 
                       begin_dt date,
                       end_dt date, 
                       constraint cl_income_pk Primary key(income_id)
                      );
Create table cl_income_inc(income_inc_id Serial not null, 
                           income_id int, 
                           begin_dt date, 
                           end_dt date,
                           constraint cl_income_inc_pk primary key(income_inc_id),
                           constraint cl_income_inc_fk1 foreign key(income_id) references cl_income(income_id)
                          );
Create table cl_income_inc_amt(income_inc_amt_id Serial not null, 
                               income_inc_id int,
                               begin_dt      date,
                               end_dt        date,
                               amt           numeric,
                               constraint cl_income_inc_amt_pk primary key(income_inc_amt_id),
                               constraint cl_income_inc_amt_fk1 foreign key(income_inc_id) references cl_income_inc(income_inc_id)
                              );
                      
CREATE TABLE

CREATE TABLE

CREATE TABLE

Insert into cl_income(begin_dt,end_dt) values ('2021-01-01','9999-12-31');
Insert into cl_income_inc(income_id,begin_dt,end_dt) values (1,'2021-01-01','9999-12-31')
                                                           ,(1,'2022-05-01','9999-12-31');
Insert into cl_income_inc_amt(income_inc_id,begin_dt,end_dt,amt) values (1,'2021-01-01','9999-12-31',1.30)
                                                                       ,(1,'2021-02-01','9999-12-31',1.50)
                                                                       ,(2,'2022-01-01','9999-12-31',1.75);

INSERT 0 1

INSERT 0 2

INSERT 0 3

select * from cl_income;

income_id
begin_dt
end_dt

1
2021-01-01
9999-12-31

SELECT 1

select * from cl_income_inc;

income_inc_id
income_id
begin_dt
end_dt

1
1
2021-01-01
9999-12-31

2
1
2022-05-01
9999-12-31

SELECT 2

select * from cl_income_inc_amt;

income_inc_amt_id
income_inc_id
begin_dt
end_dt
amt

1
1
2021-01-01
9999-12-31
1.30

2
1
2021-02-01
9999-12-31
1.50

3
2
2022-01-01
9999-12-31
1.75

SELECT 3

income_inc_amt_id	income_inc_id	begin_dt	end_dt	amt
1	1	2021-01-01	9999-12-31	1.30
2	1	2021-02-01	9999-12-31	1.50
3	2	2022-01-01	9999-12-31	1.75