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 1uYmEP-00BBqc-Rs for pgsql-hackers@arkaria.postgresql.org; Mon, 07 Jul 2025 13:50:05 +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 1uYmEN-002Pij-7C for pgsql-hackers@arkaria.postgresql.org; Mon, 07 Jul 2025 13:50:03 +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 1uYmEM-002Pia-TO for pgsql-hackers@lists.postgresql.org; Mon, 07 Jul 2025 13:50:03 +0000 Received: from mail-wm1-x32c.google.com ([2a00:1450:4864:20::32c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uYmEL-0062z3-1j for pgsql-hackers@lists.postgresql.org; Mon, 07 Jul 2025 13:50:02 +0000 Received: by mail-wm1-x32c.google.com with SMTP id 5b1f17b1804b1-450ce671a08so15919575e9.3 for ; Mon, 07 Jul 2025 06:50:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1751896200; x=1752501000; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=vGx1D1624MTlSvN0rApScp0MrezPHeeZOT68fRDdKlg=; b=PRVZeulWmgdCV2A7SYIAv73R7KU6niiSqpMMU7eXwf/L3zF/24zHdpIWyt0ysRCZHm t+U6Rc4imuBFsk2V2KpQAB9ofiCgAUT0B72iFdqZLGTAGE7hs1CAF1MtURD6drT2sy0K hgJqzJQ4X+U4R3LnLRBaAxvtCFGGwoQAVLWbOTPkksPq5L+qdtEE2j6H5/RnExT6PVry JglV5aMhz2jDwsZP2JogxlspNQa6sBiPnAud0CiQv8GRguWsygFsF0xNvo+ZQkvCKu/z DUvL49dDM6OQgqu9Oynoie7q/MszUTTYDYYdCiwx3fUc26eoJ+QoXqru8wunmIzXlPA2 AqhQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751896200; x=1752501000; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=vGx1D1624MTlSvN0rApScp0MrezPHeeZOT68fRDdKlg=; b=cIeuASnqhjJ+vRd7Cofu0jL4eP869j1W1b7uRsHa5KiQQ+XsK38xUnfPjdoBwEk+is vbRRq/jNcM8ULN4qCOdaHprU7zkePwPbKsGQJY7D7KV5rpuaZ6Hjp/Vwz3c0/kOKvHdO /TylJEgFfEJey5ohBTrIJaWWycJJU3N8UBZp8YNJK2tlsnZhhrw10ySJexQDXweo7efq S9MQcM92UiPKiDF+SBm+UoCYt24nmh3BCdAzihdD2+fV4REevcehu6HG0Zp8R3M4QOFH uQfIF82KNNRAoqg7+C2Q/aGZvmUrBimEZ5a0Ry29c/TWJTzfa2oLzyriQsckBJX6Z1ER T8Cw== X-Forwarded-Encrypted: i=1; AJvYcCVovLW0M5+tQBh3xsQWhqTn6D/QTGJhLJsLJSzYaohscrhcnPUcd0z3LvKCJ3sOeXmJQ46FO/NNxRJM7P+Q@lists.postgresql.org X-Gm-Message-State: AOJu0YzjEWL8FfCGOez1p638rQTTUn/keI8NrFBwgUlpK4h41556qLEK e9v4VZB3Z7jjhmQo/5FVGwDyrlOvUHBgW3TvG8d0nL/rVKJfcs84ToP/ X-Gm-Gg: ASbGnctbH2wqVCyjhQRPLhhjV0PZVBbmq8UtcRHqwUuTaxo1Pp5ZfhHpJzk9gjq9zIX DKRzPm3xqoYhJ7w7GSOk2r+Y0+gngHuPuYdw/9Rkw/1KKjv+ib4yNfRUWqvDdPzloTg7aAba/6s HaQpA/rMAHoQylihULTSH8pZdEBbNfV0Xkn9qzqeeIprgjZqHp0bSzqXe5HCdA0Qxh3mjO6Taak tyn3Z2ENKIWiBF1zW11yXLw5QKYJOQhMhWrL5G/7k255x6EOkGDhRPtce395h5uxB0ykufzsHOl QCBxz88xVEKTB1y4h+qpjD6sgnVfO6wPE7Y+V2FrhgYALPwrUJwkjOBsXwSqUtd4w+aSZC8GEA= = X-Google-Smtp-Source: AGHT+IFlo3F0QlMEBFIsYvVfEJEFcuSi8hlq5nShz7nm1EQEU+3Zc3nY7d2InwZH1ZM192DXp0P3dQ== X-Received: by 2002:a05:600c:83cc:b0:440:61eb:2ce5 with SMTP id 5b1f17b1804b1-454b4ea6cbdmr123999675e9.17.1751896199935; Mon, 07 Jul 2025 06:49:59 -0700 (PDT) Received: from [192.168.15.186] ([80.251.191.198]) by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-454b188df09sm110900675e9.37.2025.07.07.06.49.59 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Jul 2025 06:49:59 -0700 (PDT) Message-ID: <6642af90-561c-4f0c-9d5b-7e288e6e7f84@gmail.com> Date: Mon, 7 Jul 2025 15:49:58 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment To: Ilia Evdokimov , David Rowley Cc: Robert Haas , Tom Lane , Lukas Fittl , Daniel Gustafsson , PostgreSQL Hackers , Nikolay Samokhvalov References: <81ef0f5a-b6a1-4417-ae3a-2bae9b8d419e@gmail.com> <67b5c84a-dedd-4103-af7e-0117046102fe@tantorlabs.com> <580fe72c-85be-45a8-9e00-6b5417690557@tantorlabs.com> <3ea66a50-e4b0-4f20-ae0c-4d9cbaf81cfb@tantorlabs.com> <0e8dc4fa-3655-4e9f-9424-9b32a15e6acd@tantorlabs.com> <7e995158-157e-4ec6-a7c8-266c1a6f7b43@tantorlabs.com> <06840539-e321-4930-9f89-cfc783c5b80a@tantorlabs.com> <15e0422a-31ec-4e82-b0cf-0839f636b185@tantorlabs.com> <5c7034c4-c48b-428b-ab3f-67f14d9f542e@tantorlabs.com> Content-Language: en-US From: Andrei Lepikhov In-Reply-To: <5c7034c4-c48b-428b-ab3f-67f14d9f542e@tantorlabs.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/7/2025 10:30, Ilia Evdokimov wrote: > On 01.05.2025 15:22, Ilia Evdokimov wrote: > >> I attached updated v9 patch with the suggested changes. The updated >> line in the EXPLAIN looks like this: >> >> Text format: >>     Estimates: capacity=1 distinct keys=1 lookups=2 hit percent=50.00% >> >> Non-text format: >>     Estimated Capacity: 1 >>     Estimated Distinct Lookup Keys: 1 >>     Estimated Lookups: 2 >>     Estimated Hit Percent: 50.00 >> >> Any suggestions? > > > I attached rebased v10 patch on 5a6c39b. Exposing internal information about the estimation of the number of groups in the Memoise node, shouldn't we do the same in even more vague cases, such as IncrementalSort? For example, in [1] (see its attachment), I observe that IncrementalSort is considerably better than Sort, but has a larger cost. It would be helpful to understand if an incorrect ngroups estimation causes this. [1] https://www.postgresql.org/message-id/a74d2648-c93e-4686-a8d4-2843515b8702@gmail.com -- regards, Andrei Lepikhov