Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pwK6Z-0008Jz-U9 for pgsql-general@arkaria.postgresql.org; Tue, 09 May 2023 09:58:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pwK6X-0000tj-Nk for pgsql-general@arkaria.postgresql.org; Tue, 09 May 2023 09:57:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pwK6X-0000ta-C5 for pgsql-general@lists.postgresql.org; Tue, 09 May 2023 09:57:57 +0000 Received: from smtp-relay-00-9.dondominio.net ([31.214.176.22]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pwK6U-001MkR-Hj for pgsql-general@lists.postgresql.org; Tue, 09 May 2023 09:57:56 +0000 Received: from localhost (smtp-relay-local.scip.local [127.0.0.1]) by smtp-relay.dondominio.com (Postfix) with SMTP id 6768C42708 for ; Tue, 9 May 2023 11:57:48 +0200 (CEST) Received: from mail-node.dondominio.com by smtp-relay.dondominio.com (Postfix) with ESMTP id 28D42422F9 for ; Tue, 9 May 2023 11:57:47 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=negora.com; s=dddk; t=1683626267; bh=bz0kaYDMKMzarA5bmW4lBurWL8jKjA2LqCFmZ2dEq/0=; h=Content-Type:Message-ID:Date:Subject:To:From; b=PkSK5MFURi4qjUEt2jk/TZS4QFgGMOLYmEki/ZOLLdOdClz2YGHtOpHqVHVB+4LnN F5QKEIItBwmRxEi+LVELOxcONch9zOSHC1fr/4YOoah0HNg4Yc7tL7SyqKBcl3tL4K Vs/iS31mfyjI57Sly03yBsprSNSIjusaYZ9f0cz0= Received: from [10.8.0.6] (67.red-83-58-241.dynamicip.rima-tde.net [83.58.241.67]) (Authenticated sender: public@negora.com) by mail-node.dondominio.com (Postfix) with ESMTPA id 0829340546 for ; Tue, 9 May 2023 11:57:46 +0200 (CEST) Content-Type: multipart/alternative; boundary="------------3MP3f3L0Xsr4pFKerRU5W9gV" Message-ID: <7818bc4a-fe6e-e750-8e9f-c0c2a660e61e@negora.com> Date: Tue, 9 May 2023 11:57:46 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Return rows in input array's order? Content-Language: en-US, es-ES, en-GB To: pgsql-general@lists.postgresql.org References: <5C5270F1-4323-458F-99C2-1D187039EA9C@gmail.com> From: negora In-Reply-To: X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.6.2 (mail-node.dondominio.com [0.0.0.0]); Tue, 09 May 2023 11:57:47 +0200 (CEST) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------3MP3f3L0Xsr4pFKerRU5W9gV Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi Dominique: Take a look to the "unnest()" function. It transforms an array into a set of rows. I believe I used it in the past to do something similar to what you need. Another option is to use a "values" expression (in a subquery) instead of an array, and build the query dynamically. Best regards. On 09/05/2023 11:37, Dominique Devienne wrote: > On Tue, May 9, 2023 at 11:23 AM David Wheeler > 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... --------------3MP3f3L0Xsr4pFKerRU5W9gV Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi Dominique:

Take a look to the "unnest()" function. It transforms an array into a set of rows. I believe I used it in the past to do something similar to what you need.

Another option is to use a "values" expression (in a subquery) instead of an array, and build the query dynamically.

Best regards.


On 09/05/2023 11:37, Dominique Devienne wrote:
On Tue, May 9, 2023 at 11:23 AM David Wheeler <hippysoyboy@gmail.com> 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...
--------------3MP3f3L0Xsr4pFKerRU5W9gV--