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 namechanges (
  id text,
  new_name text,
  change_date timestamp
);
INSERT INTO namechanges VALUES
  ('x', 'alice', '2020-03-01'),
  ('y', 'Bob T.', '2020-03-03'),
  ('x', 'Alice', '2020-03-05'),
  ('z', 'charlie', '2020-03-07'),
  ('x', 'Alice C.', '2020-03-09'),
  ('z', 'Charlie Z.', '2020-03-11');
6 rows affected

select distinct on (id) *
from namechanges
order by id, change_date desc;


id
new_name
change_date


x
Alice C.
2020-03-09 00:00:00

y
Bob T.
2020-03-03 00:00:00

z
Charlie Z.
2020-03-11 00:00:00

id	new_name	change_date
x	Alice C.	2020-03-09 00:00:00
y	Bob T.	2020-03-03 00:00:00
z	Charlie Z.	2020-03-11 00:00:00