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 mytable (
id int,
type char
);
INSERT INTO mytable VALUES
(1, 'y'),
(1, 'x'),
(2, 'z');
SELECT * FROM mytable;
3 rows affected
| id | type |
|---|---|
| 1 | y |
| 1 | x |
| 2 | z |
SELECT DISTINCT ON (id)
id,
type
FROM mytable m
JOIN (VALUES (1, 'x'), (2, 'y'), (3, 'z')) AS r (rank_id,value)
ON r.value = m.type
ORDER BY id, r.rank_id
| id | type |
|---|---|
| 1 | x |
| 2 | z |