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');
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 |