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