algorithm - What is the performance of arbitrary ordering in PostgreSQL? -

in postgres in possible perform order so

select * mytable id in (8, 6, 7, 5, 10, 24) order id=8 desc, id=6 desc, id=7 desc, id=5 desc, id=10 desc, id=24 desc; 

to select arbitrary data in arbitrary order.

i have figured if sorting algorithm has has o(log n), , naively indexof sort so:

data.sort(function(a, b) {     return indexof(a) < indexof(b); }); 

then each sorting operation may take o(2n), making our total algorithmic time o(n log n).

we can create simple index of values positions instead of resorting each time. assuming has worst time of o(log n) well, get, our sorting algorithm, o((log n)(log n)) or o((log n)^2). not great performance algorithm.

what algorithm, performance, postgres use? if better o((log n) * the_sort_algorithms_performance), implement sorting outside of db. alternatively, if algorithm 1 can port java may still not sorting in postgres.

tldr; not going details broad question. sort algorithms complex field.

as query: if provide list of values, can considerably cheaper, because have pass values in some order anyway:

select t.*   unnest('{8, 6, 7, 5, 10, 24}'::int[]) id join   mytable t using (id); 

this works, there no guarantees. sure (in postgres 9.4+):

select *   unnest('{8, 6, 7, 5, 10, 24}'::int[]) ordinality x(id, rn) join   mytable t using (id) order  x.rn; 

details:

• postgresql unnest() element number