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

select version();


version


PostgreSQL 12.0 (Debian 12.0-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


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 * FROM namechanges
ORDER  BY id, change_date DESC


id
new_name
change_date


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

x
Alice
2020-03-05 00:00:00

x
alice
2020-03-01 00:00:00

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

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

z
charlie
2020-03-07 00:00:00


SELECT 
ROW_NUMBER() OVER (PARTITION BY id  ORDER BY id, change_date DESC), * FROM namechanges
ORDER  BY id, change_date DESC


row_number
id
new_name
change_date


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

2
x
Alice
2020-03-05 00:00:00

3
x
alice
2020-03-01 00:00:00

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

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

2
z
charlie
2020-03-07 00:00:00


SELECT * FROM
(
  SELECT 
  ROW_NUMBER() OVER (PARTITION BY id  ORDER BY id, change_date DESC) as rn,
  id, new_name, change_date
  FROM namechanges
  ORDER  BY id, change_date DESC
) AS tab
WHERE rn = 1
ORDER BY id, change_date;


rn
id
new_name
change_date


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

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

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

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