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 1rj23L-00341x-Mo for pgsql-general@arkaria.postgresql.org; Sat, 09 Mar 2024 19:08:16 +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 1rj23K-001sFA-8K for pgsql-general@arkaria.postgresql.org; Sat, 09 Mar 2024 19:08:14 +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 1rj23J-001sF2-UC for pgsql-general@lists.postgresql.org; Sat, 09 Mar 2024 19:08:14 +0000 Received: from mail-wm1-x332.google.com ([2a00:1450:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rj23D-003g8t-QW for pgsql-general@lists.postgresql.org; Sat, 09 Mar 2024 19:08:12 +0000 Received: by mail-wm1-x332.google.com with SMTP id 5b1f17b1804b1-413183f5e11so9312835e9.1 for ; Sat, 09 Mar 2024 11:08:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1710011287; x=1710616087; darn=lists.postgresql.org; h=content-transfer-encoding:subject:from:to:content-language :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=ACRwuaMut/d/2FGKHWgB5VoiWYHHidA/LD8pzO1gy9o=; b=B0mh1s+tGC86J3/z9hADqQxMN0AhbuoyEuwY3D/8f+Z+RKmURzl/gD8OyE9KMxrk7w sscmUjf8FeNdCIAI2TyYFBC0X+O9p3ukfbv+5nw4G35zF326lCGRILua34OEj8X4aZlR B4XpOpmEaxQ1fAX3Sg3QPKcdTIGNEB0yz72STdWK0VUja180iKSEFO0bKTO8sIyWSL7q UroH2c4tHYdkQhIV2KZGlAYg7jb6TbSTiF98ACzTO4/urg4wd2iRF42jBpPvz6gDHTCM oNyTO1K07VCwvNpruDYfpMGo0j19oXTjRYnVnRwW3F1NYXc8PG/hGPVtfrQUyaXKu5V8 qgBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1710011287; x=1710616087; h=content-transfer-encoding:subject:from:to:content-language :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=ACRwuaMut/d/2FGKHWgB5VoiWYHHidA/LD8pzO1gy9o=; b=o214YXYah7/UZhdNYKAXvUeqMAbI1n8xA/9FoizNvI5gGZ0dy9RUOWVwDkyi9Gx8aZ d6Ym1U542vxQ0Uo84X2OrCeve8VVt/U4L7sHuUP/oSl4TcPrAZioSqsWiVYF/v8F5Gmb ROtdrDgOpQLotrlHEplMBELJA6R0PA7q8Emrx4yQdnDq3IzwT/HHr73rEFYV9md95sma T/pDS6U6sjB0abQ38pdrxGrFV8uVElxYCuPOmFK1xOwgaBZsIj/fUu5gSeTD/tWaXtD0 1RYQ58xaQHMuFvtOQt+ExjGpM+sXVxNPiNCna58j8Yz4Mar+Y2AFn9eHXx9cWHaSVF0l HzGA== X-Gm-Message-State: AOJu0YxQljOdoBNBAwiyuU09APStU+ZdXx8D3eh+8ul8XfVzeFcZB6h2 1Y0lD8EJo8iS1hx0ciXG3tfFNfVczEOOhupq2bzEK0tgPcbo92RiEr2IJtkEf80= X-Google-Smtp-Source: AGHT+IE6l7Q0WTE1Ku1JjFYFhWN+jTRZHTIkDWMpEXG9CSiy+WTDHj2vH+EWiUDfARGOeK79JUObtA== X-Received: by 2002:a05:600c:511d:b0:412:e0f7:ef52 with SMTP id o29-20020a05600c511d00b00412e0f7ef52mr3064933wms.19.1710011286596; Sat, 09 Mar 2024 11:08:06 -0800 (PST) Received: from [192.168.1.3] (188-169-49-227.dsl.utg.ge. [188.169.49.227]) by smtp.googlemail.com with ESMTPSA id r12-20020a05600c458c00b0041312c0cc45sm3440618wmo.0.2024.03.09.11.08.05 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 09 Mar 2024 11:08:05 -0800 (PST) Message-ID: <0cf29b7e-3e9e-4698-81e3-aad5bb37f446@gmail.com> Date: Sat, 9 Mar 2024 23:08:03 +0400 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US To: pgsql-general@lists.postgresql.org From: Ilya Basin Subject: Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)` Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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?