Postgres 16

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 author (
  id serial primary key
);
CREATE TABLE post (
  id serial primary key,
  author_id int
)

CREATE TABLE



CREATE TABLE


INSERT INTO author(id) VALUES(1), (2);
INSERT INTO post(author_id) VALUES(1);

INSERT 0 2



INSERT 0 1


SELECT author.id, (
  SELECT post.id
  FROM post
  WHERE post.author_id = author.id
  ORDER BY id
  LIMIT 1
) first_post_id
FROM author


id
first_post_id


1
1

2
null



SELECT 2


SELECT
  author.id, first_post.id first_post_id
FROM author
LEFT OUTER JOIN LATERAL (
  SELECT post.*
  FROM post
  WHERE post.author_id = author.id
  ORDER BY id
  LIMIT 1
) first_post ON true;


id
first_post_id


1
1

2
null



SELECT 2

id	first_post_id
1	1
2	null

id	first_post_id
1	1
2	null