On Tue, May 9, 2023 at 11:23 AM David Wheeler <hippysoyboy@xxxxxxxxx> wrote:
> Hi. With an integer identity primary key table,
> we fetch a number of rows with WHERE id = ANY($1),
> with $1 an int[] array. The API using that query must return
> rows in the input int[] array order, and uses a client-side
> mapping to achieve that currently.
>
> Is it possible to maintain $1's order directly in SQL? Efficiently?
We’ve done this before with an “order by array_index(id, input_array)”. I forget the actual function consider that pseudo code
Thanks David. I see how this would work.
It was only used for small arrays but never noticed any performance issues
Hmmm, sounds like this would be quadratic though...
Each call to array_index() will be O(N), so turn the sort into O(N^2) just from the array_index() calls,
without even considering the sorting itself (which I assume is O(N log N)).
I wonder whether the int[] can be turned into a pseudo table with a ROWNUM extra generated column that
would then be (LEFT) JOIN'd to the accessed table, so that the original array index is readily accessible.
Would something like this be possible in Postgres' SQL?
I could then skip the sort, return that original index as part of the select,
and thus be able to read the other columns directly in the correct client-side re-allocated vector-slot / structure...
