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 1toSOZ-00B0Wl-5I for pgsql-admin@arkaria.postgresql.org; Sat, 01 Mar 2025 19:21:08 +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 1toSOa-002fSa-79 for pgsql-admin@arkaria.postgresql.org; Sat, 01 Mar 2025 19:21:06 +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 1toSOZ-002fSR-PU for pgsql-admin@lists.postgresql.org; Sat, 01 Mar 2025 19:21:06 +0000 Received: from mout.gmx.net ([212.227.17.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1toSOS-000Oq2-0b for pgsql-admin@lists.postgresql.org; Sat, 01 Mar 2025 19:21:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1740856860; x=1741461660; i=shammat@gmx.net; bh=/TSGCsDY3LG4NMEB0diQnJ2RRRzYvAp4LoCv0VX9IJw=; h=X-UI-Sender-Class:Message-ID:Date:MIME-Version:Subject:To: References:From:In-Reply-To:Content-Type: Content-Transfer-Encoding:cc:content-transfer-encoding: content-type:date:from:message-id:mime-version:reply-to:subject: to; b=hGC9xAtpMdE8tWbMx4TCom1N2zNvrJJjVNa/rwTnLdPKk+HP4WDNx1em7s+/f2UH 2ruY5vqGEQz4xo0EeiH7hdYEyHgrZ67n2XZ7/DGQQFlZwgDRZO4e+s49obbTfRxSc 83e8B7tXaTJ6JpKBRknNNbUuMrElakBRoImkC5I9gmHY6Jc24TAewS3uXCs8Woddc RceG2j1i7mFMJMlaf9p2zAFyZdP66TNdexCTZa91WfEYVPp6P1TmGBLJvR8J4z07j L5IdXDHUaf/ZJaCk9q2ceX6bUFxZ7JJGC3RcpAV1YEWeVErVAQw0mlO+JJIa4KqrS T0oJ7z6XOCRUVDO1ng== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [192.168.178.31] ([88.217.180.170]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MdebB-1tF7i11T4T-00mimO for ; Sat, 01 Mar 2025 20:21:00 +0100 Message-ID: Date: Sat, 1 Mar 2025 20:20:59 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: fast way to run a query with 7 thousand constant values To: pgsql-admin@lists.postgresql.org References: <6f8ec837-86ad-43e0-9416-d0a86009e28c@quadratum-braccas.com> Content-Language: de-DE, en-US From: shammat@gmx.net In-Reply-To: <6f8ec837-86ad-43e0-9416-d0a86009e28c@quadratum-braccas.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:mUfMslgIN/KXw77AGVEUusSsz+6u8T/zU5cnpQsdTxnA8igbRH2 LnVRhzS2V3YwvGqcWvvXPYiLoMeP336frnyE6cNQFIkarksNO0nbAObslXVUS1bx1tLlWjW JwWyoQhKDYCcHyOh+kgd3D8InbLT8hOqF4fwwHqQwpW4BSCmwwUBvoHBIefNYSz05uUILFz durTPOQcc4EOa0pgjJV9Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:PttaGb0gtXs=;zbRXk0Gf0Ac5Bg9TTGoHlHD9I7b fagjDwimFzrtZvnkoL2wU2PrKOn4d5VeQnxe7gca9OT832TUrb60uTEpp5q+6Lq0Wqyx3tKqy QCOS8/XYN0uQvDDUXOBBkPlYbG+EDyFEGZb81oV30BcQ4Ge0gzpzl8M4RI5JQ41ZvNADo3o7L WUysLMlUoWt1LX62IIddznsVxWqFn4d9pM21KAEjnzgrNg8cpMJ4SoJPJY1Lrl0djMNuyaP7e LUnT5qOKsTkozANOIUgXwC2EfVNDVmFTHF9D1sYiSX8e5JFdj+8KTs1GVBCMaupm8PPWIZZbs yeFtk7ATqZVkWDAwr4tdfHzIXz21UpCCuqxyfRuxZmzCpOOpAhI2AM+yy8aOnYpbUAnhXSclK c4daVP4dT5n0Iy1Lk38sflEylpHxKvjpEanE5hwJlQNgIYibUnfhXjQPHu4DXI0O5hg8HJRUo J5FQFf5ANWGD5LBBFaQyjWhtzynt0qfiVPrmtyQUrx+pmc4ArSB7NZkChtoPR4K5+jZqL0xEr HNyTMf/OsSnhmO2GL7LdmFhPNl6AOhJx0ly4mtfGg48VUozN2EVJRZpkMBOCE2xksRaA3zhFv +Ddf5T1kVDT7NWfVEa1wJVEx1khlYKaL4xhttWxLQZcXROo1lwlgeBhdd2h6e0Roa499ZICOB qYNvpweGspaVQgPL5r5eAJqHFGxNiUfSDVrnUj3fI605c2qTyG2EN/2IrDTJAWGSEUHqzcZC4 YjaUDPsPyiASOYVoLAzqvo3bxrPuY+bWSLY6it9rydfnMqyReNPO9XkNi7+DFwVQXJUiq4nWL xdbTa5392rveQguZNnC6a4yL0NaeUte2qs1G+pHwvCvBLKPSVSw2yLrD+6oWDjQnZY2paJ1LN hyj8L0bhDobcM/uLoNG8ha9BKVKqv4V94S9AYT2hB9JHXP6h+XEhEovZzQbL/VatMc6DArizQ MMx0MTb6oZGunSv8qyYYVOhdyDKyqUQwP3WoQcrEKbtAPeUiJuby7o2xu2Me11TTIdUbCH90r 9Noen/z8pwz5OOwSF5RMcmAtKlpiUQ5ULvRnVDXaZccFTIv4Nm2nvu6tyZRA4TE9BOBQ67Nlb QnfE9GsuJN0jwSQiZ4mH0vdLSngDX8ifYJqz5kLL/LO1E80KXB+fUFs8GWFPCzhil/ATUgqAY w9B+GaVQ2Bb9SshSbEMcyXVZrNNxPbsJ7Yz8kVCDVtWLnWsA9mHN24vkVrgz6sdlZrv1opb1U Qf3u2zguswUdpJrdIKOg7S/9+i6cRc7Nkob5cubRt/JiZHH3WbA2YjnU+ckWZ2HefKm2NoVbV IuEBlxrTIZkq2fuPCFz58ehwdR/CfQZFm2VleaAbTakog/e6WlXv4fzB1lbyar72WEkXXp0tV HIGIzh6JmZSd83CqxT2fqXbfpQQK+Kc/I5EIAE/GFEqgdm1R0WSSau4oT4 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am 01.03.25 um 19:23 schrieb Sbob: > I have a client that wants to pass in as an IN clause a list of > 7,000 values. The value set changes for each query and it ranges > from 5,000 to 8,000 values. > > The planning time is too long for the requirements. (250 - 300ms) > > I got it to work in 50ms end to end by creating a temp table and > doing a copy from STDIN into the temp table > > However this is a Java based app and getting it to do a copy is > becoming way more complex than it should be. > > Anyone know of an alternate way to run a query where an id is one of > X values where X is a list of 5 - 8 thousand values that will not > force the planner to spend 200+ms prepping the plan? Use "where the_column =3D any(?)" and pass all the values as a single para= meter of type java.sql.Array using a PreparedStatement.