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 users (id int PRIMARY KEY, username text UNIQUE);

INSERT INTO users (id, username) VALUES
   (13589, 'foo')
 , (16674, 'bar')
 , (13588, 'baz')
 , (18888, 'bang');
4 rows affected

SELECT u.*
FROM   unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS x(id, order_nr)
JOIN   users u USING (id)
ORDER  BY x.order_nr


id
username


13589
foo

16674
bar

13588
baz


-- Postgres 9.3 and older
SELECT u.*
FROM  (
   SELECT arr, generate_subscripts(arr, 1) AS order_nr
   FROM  (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
   ) x
JOIN   users u ON u.id = x.arr[x.order_nr]
ORDER  BY x.order_nr;


id
username


13589
foo

16674
bar

13588
baz