Search Postgresql Archives

Re: Return rows in input array's order?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, May 9, 2023 at 6:36 AM David Wheeler <hippysoyboy@xxxxxxxxx> wrote:

>> It was only used for small arrays but never noticed any performance issues
>
> Hmmm, sounds like this would be quadratic though...

True, but it’s cpu time not io, which tends to be orders of magnitude slower

> 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?

The unnest function mentioned above has a “with ordinality” option which gives easy access to the array index so a simple join would do the trick

I've actually used this approach (array_index) for hundreds of items without even noticing.
The other approach would be to sort your $1 list, and then just do ORDER BY ID? 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux