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 1rjQ8w-005S52-6b for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 20:51:38 +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 1rjQ8u-006N4H-C6 for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 20:51:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjQ8u-006N1X-0j for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 20:51:36 +0000 Received: from mail-lj1-x233.google.com ([2a00:1450:4864:20::233]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rjQ8l-003q3A-Gs for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 20:51:34 +0000 Received: by mail-lj1-x233.google.com with SMTP id 38308e7fff4ca-2d2505352e6so54068861fa.3 for ; Sun, 10 Mar 2024 13:51:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1710103886; x=1710708686; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:cc:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=cHc2Mmt8LoFdPaPrTQ6H+xPLSZfQ+2otP3/JIc7hGwI=; b=Hxa1/4Fr2JSyE/TvAJvBdGs8Bx1KLszFzIIPaPGoD4EQs3l8FepNEsEk8Un89L5kyD VrLNnytacgkbopsW1yCifsgjxdch9BRRRAkEqTL4RtUSIvAMtuPlQAogHci9Lyp0pTvh Wjl6p1uB6q4tpmyGUjuGcceEOFC71o/hdyw4X91Xh/UCn0jWzV2QLBXJXpgA216C10Dx IRDNclkVb0jPMG8DXgg1LNfQef81vsQ/hVBOpNtmKHD5mtjr1pe35Z6miZanmCZgCT6g kWIrG8HwEMPrVfVBFwghFVEi8XSa22MMO73Ft1L4jvwGFVIxITnI/DqLaKhzg8AKPQrw s0jw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710103886; x=1710708686; h=content-transfer-encoding:in-reply-to:from:cc:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=cHc2Mmt8LoFdPaPrTQ6H+xPLSZfQ+2otP3/JIc7hGwI=; b=N6iEKEXUkx3kj8ffigu/Dsdv9HhfZCttVge8v8jaCXfzVsAkkSkDtCia0D8r5asToe 5Szpbw8mn359ih0CWndfJVP1Masw9qFwCDie03mgeTSGv/ATgfFlGzbAX43ywKf1jQex n81p8Vg4BFMZqG84tqS/+Y8VyHe4XGtTmv8I5hDcWGkj4armNfWyudWBtMc2zLo3buau ul1o9OuQTwd0cv+MUrkyZPIvM+nvUtXbYr6Pw7i7ihCJnHBP4e3KQ/tc/BTpNia+CVQ4 D2xoVShppjLwGENZyEkCrqRh01CmXD14NLvw80irxeVL9A9usffqfV89+8SICF82weAw fPBg== X-Gm-Message-State: AOJu0YyvnN8HEcsMAQBJDqfCgJn2GDsxi6xcMtz6pFRcGMkNcCiAHZhR c/8gfHiKJ/t2sgIl4bkeuE8R/9mgSbZt74J5Q0C4dGqa2arg25fS X-Google-Smtp-Source: AGHT+IG7eDuE9aHOjQ2QnUQ62pyYvqsrrWgQiBIUBUPsJOwmRj8oiDO2LDs4YCs1D4COWFIy7Fh5xw== X-Received: by 2002:a2e:9dc3:0:b0:2d4:299c:cebb with SMTP id x3-20020a2e9dc3000000b002d4299ccebbmr2649769ljj.47.1710103885870; Sun, 10 Mar 2024 13:51:25 -0700 (PDT) Received: from [192.168.1.3] (188-169-49-227.dsl.utg.ge. [188.169.49.227]) by smtp.googlemail.com with ESMTPSA id a16-20020a2e8610000000b002d2904e959csm828400lji.5.2024.03.10.13.51.24 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 10 Mar 2024 13:51:25 -0700 (PDT) Message-ID: <8fa3f3c5-7515-4672-b652-73167a08bbf4@gmail.com> Date: Mon, 11 Mar 2024 00:51:21 +0400 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: en-US To: Thomas Kellerer , laurenz.albe@cybertec.at References: <0cf29b7e-3e9e-4698-81e3-aad5bb37f446@gmail.com> <8f0fefa7-54ce-4bca-a1ef-84a70f0b7663@gmx.net> Cc: pgsql-general@lists.postgresql.org From: Ilya Basin In-Reply-To: <8f0fefa7-54ce-4bca-a1ef-84a70f0b7663@gmx.net> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Laurenz thanks for the info. Thomas no I can't. -------- Original Message -------- From: Thomas Kellerer [mailto:shammat@gmx.net] Sent: Sunday, March 10, 2024 at 11:58 UTC To: pgsql-general@lists.postgresql.org Subject: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)` 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 several `SELECT ... WHERE key in($1,$2,...)`. However, the proprietary ORM we use can only produce this SQL: `SELECT ... WHERE (key=$1 or key=$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 = 1 or id = 2 or id = 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 as 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 = any(?)