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?.

create table company (id smallserial primary key, name text);
insert into company (name) values 
  ('IBM'),
  ('Microsoft'),
  ('Google'),
  ('Silicon&Synapse')
returning *;

create table person (
  id smallserial primary key, 
  name text, 
  company_id smallint references company(id));
insert into person (name,company_id) values
 ('Ted',1),
 ('Bob',1),
 ('Dan',1),
 ('Edd',4)
returning *;

CREATE TABLE




id
name


1
IBM

2
Microsoft

3
Google

4
Silicon&Synapse



INSERT 0 4



CREATE TABLE




id
name
company_id


1
Ted
1

2
Bob
1

3
Dan
1

4
Edd
4



INSERT 0 4


--won't be the same person because it uses two types of order
--shows Bob with Ted's id
SELECT
  company.id,
  company.name,
  MIN(person.id) AS employee_person_id,   --min by id is Ted
  MIN(person.name) AS employee_person_name--min by name is Bob
FROM company
LEFT OUTER JOIN person ON (person.company_id = company.id)
GROUP BY company.id;


id
name
employee_person_id
employee_person_name


2
Microsoft
null
null

3
Google
null
null

4
Silicon&Synapse
4
Edd

1
IBM
1
Bob



SELECT 4


--https://stackoverflow.com/a/75743855/5298879
SELECT
  company.id,
  company.name,
  person.id,
  person.name
FROM company LEFT JOIN LATERAL
(SELECT id,name FROM person WHERE company_id=company.id LIMIT 1) person
             ON true;


id
name
id
name


1
IBM
1
Ted

2
Microsoft
null
null

3
Google
null
null

4
Silicon&Synapse
4
Edd



SELECT 4


--the above works kind of like the query below, 
--but above both id and name of person are guaranteed to be from the same row
--two independent limit 1's could potentially fetch something else
SELECT
  company.id,
  company.name,
  (select person.id   from person where person.company_id = company.id limit 1),
  (select person.name from person where person.company_id = company.id limit 1)
FROM company;


id
name
id
name


1
IBM
1
Ted

2
Microsoft
null
null

3
Google
null
null

4
Silicon&Synapse
4
Edd



SELECT 4

id	name	employee_person_id	employee_person_name
2	Microsoft	null	null
3	Google	null	null
4	Silicon&Synapse	4	Edd
1	IBM	1	Bob