Postgres 12

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 departments (department_id INTEGER, name VARCHAR);

CREATE TABLE


CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR, 
                        department_ID INTEGER, project_names TEXT[]);

CREATE TABLE


INSERT INTO departments (department_ID, name) VALUES 
    (1, 'Engineering'), 
    (2, 'Support'),
    (3, 'HR');

INSERT 0 3


INSERT INTO employees (employee_ID, last_name, department_ID) VALUES 
    (101, 'Richards', 1),
    (102, 'Paulson',  1),
    (103, 'Johnson',  2); 

INSERT 0 3


SELECT * 
FROM departments AS d,
LATERAL (SELECT  * FROM employees AS e 
         WHERE e.department_ID = d.department_ID 
         ORDER BY employee_id DESC LIMIT 1) AS iv2
ORDER BY employee_ID;


department_id
name
employee_id
last_name
department_id
project_names


1
Engineering
102
Paulson
1
null

2
Support
103
Johnson
2
null



SELECT 2


SELECT * 
FROM departments AS d
LEFT JOIN LATERAL (SELECT * FROM employees AS e 
              WHERE e.department_ID = d.department_ID 
              ORDER BY employee_id DESC LIMIT 1) AS iv2
ON TRUE
ORDER BY employee_ID;


department_id
name
employee_id
last_name
department_id
project_names


1
Engineering
102
Paulson
1
null

2
Support
103
Johnson
2
null

3
HR
null
null
null
null



SELECT 3

department_id	name	employee_id	last_name	department_id	project_names
1	Engineering	102	Paulson	1	null
2	Support	103	Johnson	2	null
3	HR	null	null	null	null