Postgres 9.6

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 emp
(
   emp_id serial primary key,
   emp_no integer,
   emp_ref_no character varying(15),
   emp_class character varying(15)
);

create table emp_detail
(
   emp_detail_id serial primary key,
   emp_id integer,
   class_no integer,
   created_at timestamp without time zone,
   constraint con_fk foreign key(emp_id) references emp(emp_id)
 );

create table class_detail
(
class_id serial primary key,
emp_id integer,
class_no integer,
col1 JSONB,
created_at timestamp without time zone default now(),
constraint cd_fk foreign key(emp_id) references emp(emp_id)
);


INSERT INTO emp(
            emp_no, emp_ref_no, emp_class)
    VALUES ('548251', '2QcW', 'abc' );

INSERT INTO emp(
            emp_no, emp_ref_no, emp_class)
    VALUES ('548251', '2FQx', 'abc');

INSERT INTO emp(
            emp_no, emp_ref_no, emp_class)
    VALUES ('548251', '2yz', 'abc');

INSERT INTO emp_detail(
            emp_id, class_no, created_at
            )
    VALUES ( 1, 2, '2018-05-04 11:00:00'
            ); 

INSERT INTO emp_detail(
            emp_id, class_no, created_at
            )
    VALUES ( 1, 1, '2018-04-04 11:00:00'
            ); 

INSERT INTO emp_detail(
            emp_id, class_no, created_at
            )
    VALUES ( 2, 1, '2018-05-10 11:00:00' 
            );

INSERT INTO emp_detail(
            emp_id, class_no, created_at 
            )
    VALUES ( 2, 2, '2018-02-01 11:00:00' 
            );

INSERT INTO emp_detail(
            emp_id, class_no, created_at
            )
    VALUES ( 3, 2, '2018-02-01 11:00:00'
            );

insert into class_detail(emp_id, class_no, col1, created_at) values(1,1,'{"Name":"Nik"}', '2018-02-01 10:00:00');

insert into class_detail(emp_id, class_no, col1, created_at) values(1,1,'{"Name":"Nik Anderson"}', '2018-03-01 10:00:00');

insert into class_detail(emp_id, class_no, col1, created_at) values(1,2,'{"Name":"James Anderson TST"}', '2018-03-15 10:00:00');

insert into class_detail(emp_id, class_no, col1, created_at) values(1,2,'{"Name":"Tim Paine ST"}', '2018-04-01 10:00:00');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM emp;


emp_id
emp_no
emp_ref_no
emp_class


1
548251
2QcW
abc

2
548251
2FQx
abc

3
548251
2yz
abc


SELECT * FROM emp_detail;


emp_detail_id
emp_id
class_no
created_at


1
1
2
2018-05-04 11:00:00

2
1
1
2018-04-04 11:00:00

3
2
1
2018-05-10 11:00:00

4
2
2
2018-02-01 11:00:00

5
3
2
2018-02-01 11:00:00


SELECT * FROM class_detail;


class_id
emp_id
class_no
col1
created_at


1
1
1
{"Name": "Nik"}
2018-02-01 10:00:00

2
1
1
{"Name": "Nik Anderson"}
2018-03-01 10:00:00

3
1
2
{"Name": "James Anderson TST"}
2018-03-15 10:00:00

4
1
2
{"Name": "Tim Paine ST"}
2018-04-01 10:00:00


SELECT DISTINCT ON (ed.emp_id)
    e.emp_id, e.emp_no, e.emp_ref_no, ed.class_no, cd.*
FROM 
    emp_detail ed
JOIN emp e ON e.emp_id = ed.emp_id
JOIN class_detail cd ON ed.class_no = cd.class_no
ORDER BY ed.emp_id, ed.created_at DESC, cd.created_at DESC


emp_id
emp_no
emp_ref_no
class_no
class_id
emp_id
class_no
col1
created_at


1
548251
2QcW
2
4
1
2
{"Name": "Tim Paine ST"}
2018-04-01 10:00:00

2
548251
2FQx
1
2
1
1
{"Name": "Nik Anderson"}
2018-03-01 10:00:00

3
548251
2yz
2
4
1
2
{"Name": "Tim Paine ST"}
2018-04-01 10:00:00

emp_detail_id	emp_id	class_no	created_at
1	1	2	2018-05-04 11:00:00
2	1	1	2018-04-04 11:00:00
3	2	1	2018-05-10 11:00:00
4	2	2	2018-02-01 11:00:00
5	3	2	2018-02-01 11:00:00

class_id	emp_id	class_no	col1	created_at
1	1	1	{"Name": "Nik"}	2018-02-01 10:00:00
2	1	1	{"Name": "Nik Anderson"}	2018-03-01 10:00:00
3	1	2	{"Name": "James Anderson TST"}	2018-03-15 10:00:00
4	1	2	{"Name": "Tim Paine ST"}	2018-04-01 10:00:00

emp_id	emp_no	emp_ref_no	class_no	class_id	emp_id	class_no	col1	created_at
1	548251	2QcW	2	4	1	2	{"Name": "Tim Paine ST"}	2018-04-01 10:00:00
2	548251	2FQx	1	2	1	1	{"Name": "Nik Anderson"}	2018-03-01 10:00:00
3	548251	2yz	2	4	1	2	{"Name": "Tim Paine ST"}	2018-04-01 10:00:00