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 1sZ1Fb-00FqbZ-8m for pgsql-hackers@arkaria.postgresql.org; Wed, 31 Jul 2024 04:47:47 +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 1sZ1FZ-000XOd-Nx for pgsql-hackers@arkaria.postgresql.org; Wed, 31 Jul 2024 04:47:45 +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 1sZ1FZ-000XOV-EJ for pgsql-hackers@lists.postgresql.org; Wed, 31 Jul 2024 04:47:45 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sZ1FS-002JjA-Kh for pgsql-hackers@lists.postgresql.org; Wed, 31 Jul 2024 04:47:44 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1722401257; bh=tQ/6clFxebT3A5WVGeNBY0Cw9weznd436bmcp1dZrAY=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=vwV8UZZh2HBdgWR0NpE5/Bad+LAP0+wTX3+7UWek1Y+HrCZTHtu9kCqJm+H89uBmg U499OHVISIZjene3v2IjXPKy44LIejygF0PsW2770bGSlTQJNd0wRhRoc1eAvmVMRt 27dDqJCfCQo8B702DE42AqOfhfmG4aWpvhLHVxGgJEH11TTb6iFt7Yeb1qvCMStuXk cNBXonBYRomRUzS1Qpvx6aEae6NH3zE5yRAn23FdRM6S5ahgBlGhAqxlXIK1C3DPe7 fkvchQljXr0KJMTEEEgZDKGL/+88HhmjiuNA+5PQurSwykFy3BVvZvCVraCnLIMJer gGIiQn6/n6t4Q== Received: from [172.30.1.222] (unknown [172.30.1.222]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) (Authenticated sender: a.melnikov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 7C6F960306; Wed, 31 Jul 2024 07:47:36 +0300 (MSK) Message-ID: <67c2ce53-022c-44e3-9e73-67d62355da01@postgrespro.ru> Date: Wed, 31 Jul 2024 07:47:33 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan To: Peter Geoghegan , Matthias van de Meent Cc: PostgreSQL Hackers , Tom Lane , Tomas Vondra , Jeff Davis , benoit , Alexander Korotkov , Heikki Linnakangas References: Content-Language: en-US From: "Anton A. Melnikov" In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-KSMG-AntiPhishing: NotDetected, bases: 2024/07/31 02:28:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/07/31 01:43:00 #26186861 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, Peter! On 20.01.2024 01:41, Peter Geoghegan wrote: > It is quite likely that there are exactly zero affected out-of-core > index AMs. I don't count pgroonga as a counterexample (I don't think > that it actually fullfills the definition of a ). Basically, > "amcanorder" index AMs more or less promise to be compatible with > nbtree, down to having the same strategy numbers. So the idea that I'm > going to upset amsearcharray+amcanorder index AM authors is a > completely theoretical problem. The planner code evolved with nbtree, > hand-in-glove. From the 5bf748b86bc commit message: > There is a theoretical risk that removing restrictions on SAOP index > paths from the planner will break compatibility with amcanorder-based > index AMs maintained as extensions. Such an index AM could have the > same limitations around ordered SAOP scans as nbtree had up until now. > Adding a pro forma incompatibility item about the issue to the Postgres > 17 release notes seems like a good idea. Seems, this commit broke our posted knn_btree patch. [1] If the point from which ORDER BY goes by distance is greater than the elements of ScalarArrayOp, then knn_btree algorithm will give only the first tuple. It sorts the elements of ScalarArrayOp in descending order and starts searching from smaller to larger and always expects that for each element of ScalarArrayOp there will be a separate scan. And now it does not work. Reproduction is described in [2]. Seems it is impossible to solve this problem only from the knn-btree patch side. Could you advise any ways how to deal with this. Would be very grateful. With the best wishes, -- Anton A. Melnikov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company [1] https://commitfest.postgresql.org/48/4871/ [2] https://www.postgresql.org/message-id/47adb0b0-6e65-4b40-8d93-20dcecc21395%40postgrespro.ru