How to sort by list id in postgresql?

Question

PostgreSQL

Hello. I'm trying to figure out how to sort a query like this:

select id, text from mytable where id in (5,13,2,1,6,10);

you need to sort in the same order as in the id list, i.e. 5,13,2,1,6,10
I found this option:

select id, text from mytable where id in (5,1,13,10) order by id=5 DESC, id=1 DESC, id=13 DESC, id=10 DESC;

but if I have 20-30 values ​​in in, then I get a very large footcloth.
Question number 2: how optimal is such a query with a large list of id's? At what size of the list should I change the query, calculate rows somehow differently?

Answer 1

select id, text from mytable where id in (5,13,2,1,6,10) order by idx(array[5,13,2,1,6,10], id);

With PostgreSQL 9.4 you can also do this:

select mt.id, mt.text from mytable as mt
join unnest('{5,13,2,1,6,10}'::int[]) with ordinality as t(id, ord) using (id)
order by t.ord;

Answer 2

Create a side by side table with two columns - id and order. Make the query connected, sort the results by order values ​​of the auxiliary table