From: | Kirk Wolak <wolakk(at)gmail(dot)com> |
---|---|
To: | David Wheeler <hippysoyboy(at)gmail(dot)com> |
Cc: | Dominique Devienne <ddevienne(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Return rows in input array's order? |
Date: | 2023-05-10 04:59:54 |
Message-ID: | CACLU5mTW3GNTGwcyZ0oELL2W68nKToJSw2WpNiqXe7FQ4bQM9A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, May 9, 2023 at 6:36 AM David Wheeler <hippysoyboy(at)gmail(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Kirk Wolak | 2023-05-10 05:02:46 | Re: ICU, locale and collation question |
Previous Message | Kirk Wolak | 2023-05-10 04:39:55 | Re: "PANIC: could not open critical system index 2662" - twice |