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 1pwKha-00023f-16 for pgsql-general@arkaria.postgresql.org; Tue, 09 May 2023 10:36:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pwKhY-0005dl-1W for pgsql-general@arkaria.postgresql.org; Tue, 09 May 2023 10:36:12 +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 1pwKhX-0005dc-Lf for pgsql-general@lists.postgresql.org; Tue, 09 May 2023 10:36:11 +0000 Received: from mail-pf1-x430.google.com ([2607:f8b0:4864:20::430]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pwKhV-001N01-AD for pgsql-general@postgresql.org; Tue, 09 May 2023 10:36:10 +0000 Received: by mail-pf1-x430.google.com with SMTP id d2e1a72fcca58-64395e741fcso5734838b3a.2 for ; Tue, 09 May 2023 03:36:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1683628568; x=1686220568; h=to:references:message-id:date:cc:in-reply-to:from:subject :mime-version:content-transfer-encoding:from:to:cc:subject:date :message-id:reply-to; bh=KpFIoqvzEouuf6TOpWqP3J2NeGuhdHBCRBgefbG7zBw=; b=RRO0ybxg/ESQl/TxbJn5yehstB4G3+J/ED9ndA09Q7Ve9ObdPfGHbgTDPXJNFjhjJl iqk8r/w4cheTro+YCv6Q8Do6ZMgBQZ0YB9aVDLPNdLVwfelHBYXco14Ir0xLGAc8tmDi yNmByo6vI0Zu2aHMQSOFloPrn3jv0oDRjSwQxIUAdN3Qny2mmxuYbpsHPp/RyPPw9v6R 6RuGrOc5u8Re1CnjTEK77wrKOhCMSKF7c91hPVFI+MNU2eD4ZPLJ7RKiAObk8K8lXkt9 tEWlytrjWJF0OGirPV+VDqFlfzf9PDfYYha8B59bDQFlKP6HCxmHp86nr5qBxV6pphw4 Elvw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1683628568; x=1686220568; h=to:references:message-id:date:cc:in-reply-to:from:subject :mime-version:content-transfer-encoding:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=KpFIoqvzEouuf6TOpWqP3J2NeGuhdHBCRBgefbG7zBw=; b=hzkg3OBNzZNenHWRuLAPUrrk1VldrrCzeTgJil4xZj/dFCpQHxIBfCmUrGMgxFnIy1 UheLNXeHJ7QxWbUXUNNu2p3IrDbCSW2f5uUZ6sIrcUzuZtZghkJSTrluKWYLdKqVBYUw sK93o2qmDotJaHx2PmFr4HXSC6vyxjvX7wOqPOWlbHDU0afXj7lk9ha5biVRjq4HV2gG POsmmNB08zYQ7tARQ70Eg9FuJ9ay21C/jgNvpbCFzOvjmMq0yOFNXwORcHqdu808qQk2 6yI7m9BGJoF4V3csSuIzMqFjd4y/3frnc6yxFwciu2ZqC5prmnprNDVHltCuH3WmYuDP +RQw== X-Gm-Message-State: AC+VfDz1+oLxBiVb6NAQTI8idqBcvdywflykSpINNOPGc72z1J+/MMRk bxmtsXS+5waNXj1AQC1oHvg= X-Google-Smtp-Source: ACHHUZ7KEYOJhFx9pC/lafTGUnSyU9gIMZp7wsWYFUNgXowQmXoNBxE6bk5LyImb4fJRusXTtBlWIA== X-Received: by 2002:a05:6a00:1396:b0:646:6cc3:4a52 with SMTP id t22-20020a056a00139600b006466cc34a52mr5670419pfg.3.1683628568153; Tue, 09 May 2023 03:36:08 -0700 (PDT) Received: from smtpclient.apple (180-150-38-128.b49626.bne.nbn.aussiebb.net. [180.150.38.128]) by smtp.gmail.com with ESMTPSA id p16-20020a62ab10000000b0063b7a0b9cc5sm1428338pff.186.2023.05.09.03.36.07 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 09 May 2023 03:36:07 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Mime-Version: 1.0 (1.0) Subject: Re: Return rows in input array's order? From: David Wheeler In-Reply-To: Cc: pgsql-general@postgresql.org Date: Tue, 9 May 2023 20:35:55 +1000 Message-Id: References: To: Dominique Devienne X-Mailer: iPhone Mail (20B101) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk >> It was only used for small arrays but never noticed any performance issue= s >=20 > Hmmm, sounds like this would be quadratic though... True, but it=E2=80=99s cpu time not io, which tends to be orders of magnitud= e 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 ar= ray index is readily accessible. > Would something like this be possible in Postgres' SQL? The unnest function mentioned above has a =E2=80=9Cwith ordinality=E2=80=9D o= ption which gives easy access to the array index so a simple join would do t= he trick