Postgres 16

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 master (m_id Int, remarks Varchar(64));
INSERT INTO master(m_id, remarks) 
  VALUES (1, 'Remarks1'),
         (2, 'Remarks2');
Select * From master;

CREATE TABLE



INSERT 0 2




m_id
remarks


1
Remarks1

2
Remarks2



SELECT 2


Create Table details (d_id Int, m_id Int, item Varchar(64), status Int);
INSERT INTO details(d_id, m_id, item, status) 
  VALUES (1, 1, 'Item1', 1),
         (2, 1, 'Item2', 2),
         (3, 1, 'Item3', 2),
         (4, 1, 'Item3', 3),
         (5, 2, 'Item1', 3),
         (6, 2, 'Item2', 3),
         (7, 2, 'Item3', 2);
Select * From details;

CREATE TABLE



INSERT 0 7




d_id
m_id
item
status


1
1
Item1
1

2
1
Item2
2

3
1
Item3
2

4
1
Item3
3

5
2
Item1
3

6
2
Item2
3

7
2
Item3
2



SELECT 7


Select      m.remarks, 
            Count(CASE WHEN d.status = 1 THEN 1 END) AS status1_cnt,
            Count(CASE WHEN d.status = 2 THEN 1 END) AS status2_cnt,
            Count(CASE WHEN d.status = 3 THEN 1 END) AS status3_cnt, 
            Count(CASE WHEN d.status = 4 THEN 1 END) AS status4_cnt
From        master m
Inner Join details d ON( d.m_id = m.m_id )
Group By    m.remarks


remarks
status1_cnt
status2_cnt
status3_cnt
status4_cnt


Remarks1
1
2
1
0

Remarks2
0
1
2
0



SELECT 2