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 1sA9F6-007qLM-UE for pgsql-hackers@arkaria.postgresql.org; Thu, 23 May 2024 14:16:30 +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 1sA9F5-00CzvY-Dw for pgsql-hackers@arkaria.postgresql.org; Thu, 23 May 2024 14:16:27 +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 1sA9F5-00CzvQ-0A for pgsql-hackers@lists.postgresql.org; Thu, 23 May 2024 14:16:27 +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 1sA9Ex-001dAS-1B for pgsql-hackers@lists.postgresql.org; Thu, 23 May 2024 14:16:25 +0000 Received: from [10.4.14.211] (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: a.rybakina@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 10E06E210FC; Thu, 23 May 2024 17:16:15 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1716473775; bh=61YC9reGKi1n83jElPLImMpA9PivObKpZs4+kxynmIk=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=zjOyIVqLGktloNtJbZHS74vCQ6JUrnideLmpJGfNzHqL5mjZZIO1WsroNOrhnFWpc Njk95uQxHxsXQEVN+8TdVJnaREpaFUAc/ezSa53otmtit1ZncO1QQB7X80ELs+1OJB 3AwIma6TkNcQMSw+BE/jaAg12A+KZMOWUYagn2v6s6zpq5vOL6Xr2OPmd3dHQ+asxM apJoA2Wp9igiGLuHay8EdRE6KAIUGWM6y6zqX/amvIk+tIRgKQWQo/NkyUca3lB9Ml ty+xvX6FGSPZhwPTBX9XdqkoOFHjBD1g20A3hTZrQB6d5dzWT4Byr/bAzbbC9i/eQ/ 6NJc9CLuwbShQ== Message-ID: <55816aa0-a493-4ff3-a1c4-52948a809712@postgrespro.ru> Date: Thu, 23 May 2024 17:16:14 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Sort operation displays more tuples than it contains its subnode To: Tom Lane , David Rowley Cc: PostgreSQL Developers References: <9f4a159b-f527-465f-b82e-38b4b7df812f@postgrespro.ru> <19449.1716412668@sss.pgh.pa.us> Content-Language: en-US From: Alena Rybakina In-Reply-To: <19449.1716412668@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-KSMG-AntiPhishing: NotDetected, bases: 2024/05/23 10:56: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/05/23 07:54:00 #25244744 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 Yes, I got it. Thank you very much for the explanation. On 23.05.2024 00:17, Tom Lane wrote: > "a.rybakina" writes: >> I faced the issue, when the sorting node in the actual information >> shows a larger number of tuples than it actually is. And I can not >> understand why? > If I'm reading this correctly, the sort node you're worrying about > feeds the inner side of a merge join. Merge join will rewind its > inner side to the start of the current group of equal-keyed tuples > whenever it sees that the next outer tuple must also be joined to > that group. Since what EXPLAIN is counting is the number of tuples > returned from the node, that causes it to double-count those tuples. > The more duplicate-keyed tuples on the outer side, the bigger the > effect. > > You can see the same thing happening at the Materialize a little > further up, which is feeding the inside of the other merge join. -- Regards, Alena Rybakina Postgres Professional: http://www.postgrespro.com The Russian Postgres Company