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 1pwhgm-00087A-F2 for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 11:08:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pwhgk-00012D-PQ for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 11:08:54 +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 1pwhgk-000124-FH for pgsql-general@lists.postgresql.org; Wed, 10 May 2023 11:08:54 +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 1pwhge-001YLN-Qy for pgsql-general@postgresql.org; Wed, 10 May 2023 11:08:52 +0000 Received: from [192.168.127.4] (port=29546 helo=erlenstar.riddles.org.uk) by lungold.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1pwhgb-0001BR-Lp; Wed, 10 May 2023 11:08:45 +0000 Received: from localhost ([127.0.0.1]:52416 helo=erlenstar.riddles.org.uk) by erlenstar.riddles.org.uk with esmtp (Exim 4.94 (FreeBSD)) (envelope-from ) id 1pwhgb-000LqR-7f; Wed, 10 May 2023 11:08:45 +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 "Wed, 10 May 2023 12:02:41 +0200") Message-ID: <87bkisa343.fsf@news-spur.riddles.org.uk> References: <87o7msabdp.fsf@news-spur.riddles.org.uk> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (berkeley-unix) Date: Wed, 10 May 2023 12:08:45 +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> Is it possible to maintain $1's order directly in SQL? >> 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; Dominique> Thanks Andrew, for spelling it out for me. Appreciated. Dominique> Also thanks to others who chimed in. Dominique> I assume that if the PK is composite, and I pass the PK Dominique> tuples as separate same-cardinality "parallel" arrays, I can Dominique> "zip" those arrays back via a multi-join using their Dominique> ordinals before joining with the composite-PK table? You don't need to, because unnest can do it for you: SELECT ... FROM unnest($1,$2,$3) WITH ORDINALITY AS u(id1,id2,id3,ord) JOIN yourtable t ON t.id1=u.id1 AND t.id2=u.id2 AND t.id3=u.id3 ORDER BY u.ord; (I did actually consider using a join on the ordinal column to implement multi-arg unnest internally, but the overhead was too much. So instead the executor knows how to do the zipping itself.) Dominique> PS: I guess the ideal plan depends both on the table itself, Dominique> but also the cardinality of the array(s) passed in as bind Dominique> variable(s) at runtime to the prepared statement, right? Yes, in the sense that what matters is what proportion of the table is being fetched. Is it likely that you'll be passing in very long lists of ids relative to the table size? Dominique> But from past posts, I got the impression the plan of a Dominique> prepared statement is "fixed", and does not depend on "bind Dominique> peeking" like it can in Oracle, to take those bound array's Dominique> cardinality into account at PQexecPrepared-time? It's a bit more complicated than that and it often depends on what the client library is doing; many clients don't do a protocol-level named prepare until after a client-side prepared statement has been used several times; and even after doing a named prepare, the planner won't try a generic plan until after several more uses. We distinguish between "generic plan" and "custom plan"; a generic plan is one produced without knowledge of the parameter values and must work for any parameter values, while a custom plan only works for one specific set of parameter values and can't usually be re-used. Custom plans take the parameter values into account both for estimation and for constant-folding optimizations. Generic plans are used after about the 5th use of a statement if the cost of the generic plan isn't worse than the average costs of the custom plans from the previous uses, plus a fudge factor representing the CPU cost of custom planning. The planning hazard in cases like this is that when doing a generic plan, the planner has no idea at all what the array cardinalities will be; it doesn't try and cache information like that from the custom plans. So it will make a zeroth-order approximation (i.e. a constant) derived by the time-honoured method of rectal extraction, and this may make the generic plan look a lot cheaper than it should. -- Andrew (irc:RhodiumToad)