On Tue, May 9, 2023 at 6:36 AM David Wheeler <hippysoy...@gmail.com> 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?
