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 1txnZR-00EqBi-K7 for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Mar 2025 13:46:57 +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 1txnZQ-006uOa-47 for pgsql-hackers@arkaria.postgresql.org; Thu, 27 Mar 2025 13:46:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1txnZP-006uOR-QM for pgsql-hackers@lists.postgresql.org; Thu, 27 Mar 2025 13:46:55 +0000 Received: from mail-wr1-x42d.google.com ([2a00:1450:4864:20::42d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1txnZN-001WUw-2h for pgsql-hackers@lists.postgresql.org; Thu, 27 Mar 2025 13:46:55 +0000 Received: by mail-wr1-x42d.google.com with SMTP id ffacd0b85a97d-3996af42857so1380929f8f.0 for ; Thu, 27 Mar 2025 06:46:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1743083212; x=1743688012; 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=ASA3kP+je77BFWC+/8pGg+WQAf7EUNDepAWlTGLuykk=; b=m3ZZZI8H+B76oaMRVAVYVAN/klBDXviKv63I5K5IxLimddLXuDkR7YbBqE3XSzo0t2 40TLJfk7XOYuFXIM0pwvn/ZdUlPrjVeYWLbG9Gvs8dAIAM3F1PoQOZgU0LRjGswStEBw Gel/pKGtMF0VfSyUWc0n8PBSMXemxUZPaEaSj91hoLtCZWAZ6bNyEzbfJWDKbpBdkPcD UuqC6t3up2z+PUHTB6oAJRk21CiZeKx4HH9AS0ocqBOj7WRJhCMSmEchH+ze+IcrfZ0t AtT6se9pAaswa2AJ387Tb0ZhL9gG6Cur7F6KPOA3Mtl2p0RSVzO0m8m9IyrBBActakCt SwZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743083212; x=1743688012; 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=ASA3kP+je77BFWC+/8pGg+WQAf7EUNDepAWlTGLuykk=; b=w9HabbAf1F6uIW2HfXhfyfyzDwQkSfELyIw2lT1cEc26WKkNQzS+fmcgTxuGjDUX7H JX2sThChC6tAwxDAlnNDrnmYj12MbVU7rSKgvdurNJKXq4ttQcQ994Z+CiRa6ElSRf1a bbmHTL85EFahO5W6qSHvT1y30NmforUTU0EKpGjgh/AboJRvkRb5A9ocTqinHlokkXsI TTQzVqQrFNuB9VFkX7gwPfpWcWp0sRJnYNajdusL+RMvDziVquHjCZIAIbg7BTLfdDh6 aX/IeedcZfR/Fk0V/uKbbRV2EMi4x0k5ZW6Ak9a8/LxOkC69mmCaQlTR0l6g25EdIk38 jdkA== X-Forwarded-Encrypted: i=1; AJvYcCWeJGoPcjMIoBJgY5oOhGzwW4FDqhvJpxNSWxQNWEJ+dVp6SVxrV+8FCKnF6B4dQo9k3pRU3qNzsMFj1j/m@lists.postgresql.org X-Gm-Message-State: AOJu0Yxzi55SUjvCvq9qlp9KoAXI2+3guW4qoTqjO0eu4+3gYmQBBVON XY1s3jEckgj94XD/gi5VYK0ak4rWxkOR2EwGKvWVc+f4z0ksOBKv X-Gm-Gg: ASbGncv/fkWCHfobTQOVIjEvmIU54jxVb9d0pUwj2N0KJ1IUdHhwIcK7JxCJPfHEOpo 8l5A/Vhl2942tNzjGz63YCGXHQGxWhEcGpby1SWtnIt5tjuzJ8KgL3u+WX0oGwwx4eOopSFs7kZ UjNPyQC8HmmsaSN6VHK4l8VcWKfva2nUK7Kx156LjKQCNwdG4sLVz0ryv9PiDQviCmk5X8MuBGG cIQRhjBCGu9VB1ZB475q6f7jCgGfhQZ95mV5m9ME1O3+lN99198/VEfEY5R9yqp5RkcezpCM2Ka MpJ28EAe42mSYHZOut3QYN9h0m0h02KRw/k/YWiVfgjGuq7DgOG72s1LPFeiEiwUOUSzJNOsmHB mK4NVYZjg+8z2CA== X-Google-Smtp-Source: AGHT+IFlaooO9emp6qwfNiUmxhbtr24kBLxnr6K9YBN3z3lz2zITRS2NY56WDpG1Kv14cvABif64wA== X-Received: by 2002:a5d:6d87:0:b0:38d:d166:d44 with SMTP id ffacd0b85a97d-39c09a37213mr367781f8f.23.1743083212377; Thu, 27 Mar 2025 06:46:52 -0700 (PDT) Received: from [192.168.0.89] (84.123.232.230.dyn.user.ono.com. [84.123.232.230]) by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-43d82f1bb75sm40209235e9.25.2025.03.27.06.46.51 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 27 Mar 2025 06:46:51 -0700 (PDT) Message-ID: <81ef0f5a-b6a1-4417-ae3a-2bae9b8d419e@gmail.com> Date: Thu, 27 Mar 2025 14:46:50 +0100 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 , Robert Haas , Tom Lane Cc: Lukas Fittl , David Rowley , Daniel Gustafsson , PostgreSQL Hackers , Nikolay Samokhvalov References: <44229379-3901-4cb0-8812-b354ef70d5e2@gmail.com> <8e12aa55-39eb-4e03-a8f7-077fe02309c3@gmail.com> <11c709e1-9b17-4c00-9ead-93609a09b9db@gmail.com> <1161645.1742854534@sss.pgh.pa.us> <1164845.1742856354@sss.pgh.pa.us> <3ba16aa7-3cb2-4289-b773-522ae2f03a77@tantorlabs.com> Content-Language: en-AU From: Andrei Lepikhov In-Reply-To: <3ba16aa7-3cb2-4289-b773-522ae2f03a77@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 3/27/25 11:12, Ilia Evdokimov wrote: > Memoize is unique in that it shows runtime statistics (hits, misses, > evictions), but reveals nothing about the planner’s expectations. We > don’t see how many distinct keys were estimated or how many entries the > planner thought would fit in memory. It is acceptable for me to have Memoize estimations covered by the COSTS option. This data may help both developers and the users. For us, it provides information on how good group estimations are and lets us identify gaps in the prediction code. A user may find insights about ways to optimise the query, detecting stale statistics or defining extended statistics. This makes it very difficult to > understand whether Memoize was a good choice or not, or how to fix it > when it performs poorly. I think user already has enough data to determine whether Memoize was the right choice - hits/misses/evictions show that. -- regards, Andrei Lepikhov