Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjHp5-004fZQ-6X for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 11:58:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rjHp2-0040l3-Cr for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 11:58:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjHp1-0040ku-WB for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 11:58:32 +0000 Received: from mout.gmx.net ([212.227.17.22]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjHoy-003kwj-Rl for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 11:58:31 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1710071906; x=1710676706; i=shammat@gmx.net; bh=AO+5XgZxbAvpIyByyYuBXu6RjKBBuQOVgP1eaICoLLc=; h=X-UI-Sender-Class:Date:Subject:To:References:From:In-Reply-To; b=uCcSWpndPQMLVib/p5elWEDjjIOhErlupiJdoq2+sZVZrYeASwxRvuqTE74TRnjQ cl+IA72ZcOoqcRo5Z8hAdb8GuO/WsSgpwId4Zfqims/5RK+yp4iXW0woG2tuMw8Lt IN9dY4CWt3bZkICGprrpSkUeLTZ84N/ymdLA1pR3un6crFit5v81eN5M32dfFebAU ZNJKFa0nwpnQkhkCCMnhVGba/Swdm8RnWVMU/4gDuRGmGkAmoB3neza4YmZRI/VUm r4Z6/TZ2MjBFMB6lznj5u6NpRjkbOExK7cfwDTYe4XQdu5nwvoHadcrB7qvn6Uc5z JoerwM026DFdnbK3vw== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [192.168.178.20] ([88.217.180.237]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MfHEJ-1rCSJV2vW7-00gsAZ for ; Sun, 10 Mar 2024 12:58:26 +0100 Message-ID: <8f0fefa7-54ce-4bca-a1ef-84a70f0b7663@gmx.net> Date: Sun, 10 Mar 2024 12:58:25 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)` Content-Language: de-DE, en-US To: pgsql-general@lists.postgresql.org References: <0cf29b7e-3e9e-4698-81e3-aad5bb37f446@gmail.com> From: Thomas Kellerer In-Reply-To: <0cf29b7e-3e9e-4698-81e3-aad5bb37f446@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:CRl5hKLQ8Y41iwwVMhiwSkeKitb0CJYXK2nxmSW1mKjd3t3OZYb V2YEDfRdX+1scbC87JjSxTUYDFVYH/MUBfz30oWmD73/YI0u0sq2A1NQjQVTYroQeE4sLx7 gPzN5g8Np2jitAiJnhaP7jFSxMpBg9QlCE56Z7tnwWg9zS7z2fkPHBmBV+0HIFrxSKhPjNp qVaAYnQAEVnXT6do02ZXw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:MywB2QIHhqQ=;YDQHGbSy1pHqploYh3C2ArBxTNT pOzpoMWvQn3CKmkdpgyw/15q0JwERAC811SDtrE4uYjpctcI6gTr/OMV5cFhGnZ+CA3VXx2Ih cdoidgcFFFke09PnEQo1EAjkYCYwZvmfXj9W+wD1oCTyp0oPwsMt1CBUuKug7k4Vj2rVCBm7g i5LPgWpusaMM7u2qw0k/zf7jAs8mhN/9bAHWp3fHM+vIemX0fzvgA0pEWxhPGyy+gzwJ4aAma pUvH9yw+aFMXUb6n1IiNVYXkxIoRUBa/wc7+iLZqgmsOkoL1PYkjwWCHs4ghdaikIW8KPcOHo rzXVWw/7v4bXW6XkAz+w4haRdk8h1QSgNk7KiSq4MRSFq2oan5NlqPcOmDE9RoWaj4DDtvjWd jYWGOwhRiwPI+d6F+nqbLWxvtpYlBM9ax84CYGFf6bMUufnartIMzFnp6dK0+PDw19v3S+fpR 2r35DJ3Ds2ONa8arRpZihtV/Kv+f1KMqfGTD+OCcyPvvUt3EdYtf+7DmtRLbYpM1FFwdfaWph yy0+zm5hHuZG2qoDe7L5hu+5HBZOx4X7peoS84ZFrpugNyrLXIc4hEIxRQZNxCofp1t8N3uYj EzIUb/JG1WiNs0pRn2eG7pB2LBTBjAGc38Hr48CT0dhcF7QQwVZq/LNcvauzhdOtHBLrUABOk eV8YJCW69YYJWT0N+CACz8ZArXyPJQiVwc18RpFGe+z23rc/PGKj3GoxV6cH3kxm+ZGKq96BL USe7s7uNGFWx23tria/Wawc3qCKLVzDdnoStAhXxg+JLi2h4GbEMgOoqjaOfkEeyqumSEBGPq kThLJiyrpnEWuxsilA8Zx1DqLvRMAXUGlS2suFLeI6rSk= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ilya Basin schrieb am 09.03.2024 um 20:08: > Hi List. > > I have a list of bigint keys and I need to retrieve rows by these keys. = Normally, I would split this list into pages of size 900 and perform sever= al `SELECT ... WHERE key in($1,$2,...)`. However, the proprietary ORM we u= se can only produce this SQL: `SELECT ... WHERE (key=3D$1 or key=3D$2 or .= ..)`. Surprisingly, PostgreSQL planner treats these two SQLs differently: > > - ```select * FROM "audittrail$referencelogline" where id in ( 1 , 2 , 3= )``` > > Index Scan > https://i.stack.imgur.com/dr8oz.png > > - ```select * FROM "audittrail$referencelogline" where id =3D 1 or id = =3D 2 or id =3D 3``` > > A lot of "Bitmap Index Scan" for each value > https://i.stack.imgur.com/dnErs.png > > Is it possible to configure PostgreSQL 12.16 to treat the second query a= s the first? Can you convince your obfuscation layer to send an array value (containing= all IDs) and change the query to: select * FROM "audittrail$referencelogline" where id =3D any(?)