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 serial primary key, 
  name text, 
  company_id smallint references company(id));

insert into person (name,company_id)
select md5(random()::text),     --random text
       floor(random()*4+1)::int --random company, out of the 4 we have
from generate_series(1,3e5,1);  --generate 300k of that

--here's what the 300k sample looks like
select * from person limit 5;

CREATE TABLE

id
name

1
IBM

2
Microsoft

3
Google

4
Silicon&Synapse

INSERT 0 4

CREATE TABLE

INSERT 0 300000

id
name
company_id

1
7989e730fde4006188e1f50cce4eef3e
1

2
fff8e556363c724033e3a0fdf92a5197
1

3
0ab9c31312e37e685740171ae4a5fc2c
4

4
c5773064340ed38b4cc87cbce414a37c
3

5
83f62bd047bc074cd9f424e68237bebf
1

SELECT 5

--Zegarek
--https://stackoverflow.com/a/75743855/5298879
EXPLAIN ANALYZE VERBOSE
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;

QUERY PLAN

Nested Loop Left Join  (cost=0.00..5452.62 rows=1310 width=70) (actual time=0.028..0.059 rows=4 loops=1)

  Output: company.id, company.name, person.id, person.name

  ->  Seq Scan on public.company  (cost=0.00..23.10 rows=1310 width=34) (actual time=0.011..0.012 rows=4 loops=1)

        Output: company.id, company.name

  ->  Limit  (cost=0.00..4.12 rows=1 width=36) (actual time=0.010..0.010 rows=1 loops=4)

        Output: person.id, person.name

        ->  Seq Scan on public.person  (cost=0.00..6343.75 rows=1538 width=36) (actual time=0.007..0.007 rows=1 loops=4)

              Output: person.id, person.name

              Filter: (person.company_id = company.id)

              Rows Removed by Filter: 2

Planning Time: 0.408 ms

Execution Time: 0.078 ms

EXPLAIN

--Mureinik, without ordering
--https://stackoverflow.com/a/75742859/5298879
EXPLAIN ANALYZE VERBOSE
SELECT c_id, c_name, p_id, p_name
FROM   (SELECT    c.id AS c_id,
                  c.name AS c_name,
                  p.id AS p_id,
                  p.name AS p_name,
                  ROW_NUMBER() OVER (PARTITION BY c.id) AS rn
        FROM      company c
        LEFT JOIN person p ON p.company_id = c.id) t
WHERE  rn = 1;

QUERY PLAN

Subquery Scan on t  (cost=42014.63..55923.05 rows=1538 width=70) (actual time=253.973..834.100 rows=4 loops=1)

  Output: t.c_id, t.c_name, t.p_id, t.p_name

  Filter: (t.rn = 1)

  ->  WindowAgg  (cost=42014.63..52079.30 rows=307500 width=78) (actual time=253.972..834.093 rows=4 loops=1)

        Output: c.id, c.name, p.id, p.name, row_number() OVER (?)

        Run Condition: (row_number() OVER (?) <= 1)

        ->  Merge Left Join  (cost=42014.63..47466.80 rows=307500 width=70) (actual time=253.962..771.203 rows=300000 loops=1)

              Output: c.id, c.name, p.id, p.name

              Merge Cond: (c.id = p.company_id)

              ->  Index Scan using company_pkey on public.company c  (cost=0.15..67.80 rows=1310 width=34) (actual time=0.004..0.057 rows=4 loops=1)

                    Output: c.id, c.name

              ->  Materialize  (cost=42014.47..43551.97 rows=307500 width=38) (actual time=253.949..670.854 rows=300000 loops=1)

                    Output: p.id, p.name, p.company_id

                    ->  Sort  (cost=42014.47..42783.22 rows=307500 width=38) (actual time=253.945..468.767 rows=300000 loops=1)

                          Output: p.id, p.name, p.company_id

                          Sort Key: p.company_id

                          Sort Method: external merge  Disk: 14720kB

                          ->  Seq Scan on public.person p  (cost=0.00..5575.00 rows=307500 width=38) (actual time=0.011..74.144 rows=300000 loops=1)

                                Output: p.id, p.name, p.company_id

Planning Time: 0.240 ms

Execution Time: 837.672 ms

EXPLAIN

--Mureinik, with ordering
--https://stackoverflow.com/a/75742859/5298879
EXPLAIN ANALYZE VERBOSE
SELECT c_id, c_name, p_id, p_name
FROM   (SELECT    c.id AS c_id,
                  c.name AS c_name,
                  p.id AS p_id,
                  p.name AS p_name,
                  ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.id ASC) AS rn
        FROM      company c
        LEFT JOIN person p ON p.company_id = c.id) t
WHERE  rn = 1;

QUERY PLAN

Subquery Scan on t  (cost=45927.43..55677.43 rows=1500 width=70) (actual time=856.110..1037.346 rows=4 loops=1)

  Output: t.c_id, t.c_name, t.p_id, t.p_name

  Filter: (t.rn = 1)

  ->  WindowAgg  (cost=45927.43..51927.43 rows=300000 width=78) (actual time=856.109..1037.339 rows=4 loops=1)

        Output: c.id, c.name, p.id, p.name, row_number() OVER (?)

        Run Condition: (row_number() OVER (?) <= 1)

        ->  Sort  (cost=45927.43..46677.43 rows=300000 width=70) (actual time=856.093..1002.605 rows=300000 loops=1)

              Output: c.id, p.id, c.name, p.name

              Sort Key: c.id, p.id

              Sort Method: external merge  Disk: 17728kB

              ->  Hash Right Join  (cost=39.48..6329.52 rows=300000 width=70) (actual time=0.033..323.342 rows=300000 loops=1)

                    Output: c.id, p.id, c.name, p.name

                    Inner Unique: true

                    Hash Cond: (p.company_id = c.id)

                    ->  Seq Scan on public.person p  (cost=0.00..5500.00 rows=300000 width=38) (actual time=0.009..87.550 rows=300000 loops=1)

                          Output: p.id, p.name, p.company_id

                    ->  Hash  (cost=23.10..23.10 rows=1310 width=34) (actual time=0.016..0.017 rows=4 loops=1)

                          Output: c.id, c.name

                          Buckets: 2048  Batches: 1  Memory Usage: 17kB

                          ->  Seq Scan on public.company c  (cost=0.00..23.10 rows=1310 width=34) (actual time=0.010..0.011 rows=4 loops=1)

                                Output: c.id, c.name

Planning Time: 0.269 ms

Execution Time: 1039.345 ms

EXPLAIN

--GMB
--https://stackoverflow.com/a/75743006/5298879
EXPLAIN ANALYZE VERBOSE
SELECT distinct on (c.id)
  c.id,
  c.name,
  p.id AS employee_person_id,
  p.name AS employee_person_name
FROM company c
LEFT OUTER JOIN person p ON p.company_id = c.id
ORDER BY c.id, p.id;

QUERY PLAN

Unique  (cost=45927.43..47427.43 rows=1310 width=71) (actual time=1297.026..1537.096 rows=4 loops=1)

  Output: c.id, c.name, p.id, p.name

  ->  Sort  (cost=45927.43..46677.43 rows=300000 width=71) (actual time=1297.024..1478.972 rows=300000 loops=1)

        Output: c.id, c.name, p.id, p.name

        Sort Key: c.id, p.id

        Sort Method: external merge  Disk: 17664kB

        ->  Hash Right Join  (cost=39.48..6329.52 rows=300000 width=71) (actual time=0.034..264.058 rows=300000 loops=1)

              Output: c.id, c.name, p.id, p.name

              Inner Unique: true

              Hash Cond: (p.company_id = c.id)

              ->  Seq Scan on public.person p  (cost=0.00..5500.00 rows=300000 width=39) (actual time=0.009..105.184 rows=300000 loops=1)

                    Output: p.id, p.name, p.company_id

              ->  Hash  (cost=23.10..23.10 rows=1310 width=34) (actual time=0.017..0.018 rows=4 loops=1)

                    Output: c.id, c.name

                    Buckets: 2048  Batches: 1  Memory Usage: 17kB

                    ->  Seq Scan on public.company c  (cost=0.00..23.10 rows=1310 width=34) (actual time=0.011..0.012 rows=4 loops=1)

                          Output: c.id, c.name

Planning Time: 0.438 ms

Execution Time: 1539.066 ms

EXPLAIN
id	name	company_id
1	7989e730fde4006188e1f50cce4eef3e	1
2	fff8e556363c724033e3a0fdf92a5197	1
3	0ab9c31312e37e685740171ae4a5fc2c	4
4	c5773064340ed38b4cc87cbce414a37c	3
5	83f62bd047bc074cd9f424e68237bebf	1