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 1r8cRX-00Aqud-3y for pgsql-hackers@arkaria.postgresql.org; Thu, 30 Nov 2023 08:30:43 +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 1r8cRV-00Foim-RS for pgsql-hackers@arkaria.postgresql.org; Thu, 30 Nov 2023 08:30:41 +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 1r8cRV-00Foid-Dp for pgsql-hackers@lists.postgresql.org; Thu, 30 Nov 2023 08:30:41 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r8cRS-0084gp-86 for pgsql-hackers@postgresql.org; Thu, 30 Nov 2023 08:30:40 +0000 Received: from [10.10.20.124] (node-pnr.pool-101-109.dynamic.totinternet.net [101.109.129.231]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: a.lepikhov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 9F6D3E21019; Thu, 30 Nov 2023 11:30:31 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1701333034; bh=vjnL74kvmwWRUIPgHuNx+6pcIz+QNkBjsbm3vVsvrNU=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=Ml7UgErcKaN/SRhXLP5k7+LDBQ4XvKozWh3ZKw8ysk4kqBJ66nSWlincstzaPhjhT Z1saDGtFn4lBYDA8IiG6dCnsjaLXvnkvFzlESj2XUq+L3PpyyqpuYKxOfjv3VhoccW uhHO2oUm91cIo6DF3IzG5r8sY9Ssq7NNteqCHjgxpFi8D7SQzL824VZvdxHp3J+aE/ hwrXmaBfznqHxFduhPw52Bc8U2E7GIw85m0KQIPbxZ95F9xQa7N6g7HiNJxyP14x7M LX5vZok4KNZYeHq/YDESqVvD7ibRXenhvon7qzBg9iZjeUN/EGZVoG6vJ5pT0J39eJ /F2t8O3BuXVCQ== Message-ID: <8ff70ada-4ec7-436a-8bf8-d1c7d8551ff1@postgrespro.ru> Date: Thu, 30 Nov 2023 15:30:28 +0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: POC, WIP: OR-clause support for indexes Content-Language: en-US To: Alena Rybakina , Robert Haas , pgsql-hackers@postgresql.org, Alexander Korotkov Cc: Peter Geoghegan , "Finnerty, Jim" , Marcos Pegoraro , teodor@sigaev.ru, Ranier Vilela , Tomas Vondra , Peter Eisentraut References: <567ED6CA.2040504@sigaev.ru> <16a7e3aa-24c0-4986-8820-ea2857bd7b6b@postgrespro.ru> <421656a9-5df7-41bb-9d58-1c1edbd5dc3e@postgrespro.ru> <28c727ba-5260-42a8-88b0-4ecdeeca6ef8@postgrespro.ru> <4c1c2c0b-802f-45ae-a7cd-007bfd8957cf@postgrespro.ru> <52f0d302-7963-452f-8c5a-f66d261514eb@postgrespro.ru> <43ad8a48-b980-410d-a83c-5beebf82a4ed@postgrespro.ru> <04ef16eb-46c1-4ce7-9f68-d1c80ef0be81@postgrespro.ru> <59e67a40-95a8-4d74-ae4e-027ea0f59084@postgrespro.ru> From: Andrei Lepikhov Organization: Postgres Professional In-Reply-To: <59e67a40-95a8-4d74-ae4e-027ea0f59084@postgrespro.ru> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 30/11/2023 15:00, Alena Rybakina wrote: > 2. The second patch is my patch version when I moved the OR > transformation in the s index formation stage: > > So, I got the best query plan despite the possible OR to ANY > transformation: If the user uses a clause like "x IN (1,2) AND y=100", it will break your 'good' solution. In my opinion, the general approach here is to stay with OR->ANY transformation at the parsing stage and invent one more way for picking an index by looking into the array and attempting to find a compound index. Having a shorter list of expressions, where uniform ORs are grouped into arrays, the optimizer will do such work with less overhead. -- regards, Andrei Lepikhov Postgres Professional