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 1pweZh-0006nd-Nf for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 07:49:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pweZg-0004YY-HU for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 07:49:24 +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 1pweZg-0004YO-78 for pgsql-general@lists.postgresql.org; Wed, 10 May 2023 07:49:24 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pweZc-001WoG-SP for pgsql-general@postgresql.org; Wed, 10 May 2023 07:49:22 +0000 Received: from [192.168.127.4] (port=23764 helo=erlenstar.riddles.org.uk) by lungold.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1pweZY-0000wp-CY; Wed, 10 May 2023 07:49:16 +0000 Received: from localhost ([127.0.0.1]:31501 helo=erlenstar.riddles.org.uk) by erlenstar.riddles.org.uk with esmtp (Exim 4.94 (FreeBSD)) (envelope-from ) id 1pweZX-000KuV-Uk; Wed, 10 May 2023 07:49:16 +0000 From: Andrew Gierth To: Dominique Devienne Cc: pgsql-general@postgresql.org Subject: Re: Return rows in input array's order? In-Reply-To: (Dominique Devienne's message of "Tue, 9 May 2023 10:34:10 +0200") Message-ID: <87o7msabdp.fsf@news-spur.riddles.org.uk> References: User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (berkeley-unix) Date: Wed, 10 May 2023 08:49:16 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk >>>>> "Dominique" == Dominique Devienne writes: Dominique> Hi. With an integer identity primary key table, Dominique> we fetch a number of rows with WHERE id = ANY($1), Dominique> with $1 an int[] array. The API using that query must return Dominique> rows in the input int[] array order, and uses a client-side Dominique> mapping to achieve that currently. Dominique> Is it possible to maintain $1's order directly in SQL? Dominique> Efficiently? This is the correct way: SELECT ... FROM unnest($1) WITH ORDINALITY AS u(id,ord) JOIN yourtable t ON t.id=u.id ORDER BY u.ord; This doesn't assume there won't be holes (if you want, you can change it to a left join to get a null row instead for missing ids). The query plan you get for this should be something like: Nested Loop Function Scan on unnest Index Scan on yourtable_pkey (less likely, depending on table sizes, would be a Merge Join with similar inputs. If your table is very small you might get a hashjoin and separate sort, but that shouldn't happen with realistic data sizes.) Notice that this is entirely deterministic about the output ordering without needing to do any sorting. (The planner knows that the output of WITH ORDINALITY function scans is automatically ordered by the ordinal column, so it will usually generate plans that take advantage of that.) The presence of "ORDER BY u.ord" ensures that the output order is correct regardless of plan choice. -- Andrew (irc:RhodiumToad)