Message-ID: <8e12aa55-39eb-4e03-a8f7-077fe02309c3@gmail.com>
Date: Thu, 20 Mar 2025 19:54:07 +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 <ilya.evdokimov@tantorlabs.com>,
 David Rowley <dgrowleyml@gmail.com>
Cc: Lukas Fittl <lukas@fittl.com>, Daniel Gustafsson <daniel@yesql.se>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: 
 <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
 <CAApHDvrBctjUFjY77BFd_MqPD+yf4nOZSKzbdzP70XntkknY8A@mail.gmail.com>
 <51A15CD7-E31B-483D-B911-D0EB9F5FF952@yesql.se>
 <CAP53Pkz-sK8En2c05rJDcV7pz8+OsbNqOnm_SO4tsuwm=n=3OQ@mail.gmail.com>
 <bea2a2ae-3a95-4740-8926-837bacc7bd4e@tantorlabs.com>
 <CAApHDvpq2gOt3gnzkd4Ud=wrT7YRGrF3zb29jgWzDsYEhETrOA@mail.gmail.com>
 <44229379-3901-4cb0-8812-b354ef70d5e2@gmail.com>
 <f68e7271-4648-4e0f-882f-f4e93bc9c9cf@tantorlabs.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <f68e7271-4648-4e0f-882f-f4e93bc9c9cf@tantorlabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/8e12aa55-39eb-4e03-a8f7-077fe02309c3%40gmail.com>
Precedence: bulk

On 20/3/2025 15:03, Ilia Evdokimov wrote:
> 
> On 20.03.2025 15:32, Andrei Lepikhov wrote:
>> I quite frequently need the number of distinct values (or groups) 
>> predicted during the Memoize node creation to understand why caching 
>> is sometimes employed or not.
>> But I had thought about an alternative way: having an extensible 
>> EXPLAIN (thanks to Robert), we may save optimisation-stage data (I 
>> have the same necessity in the case of IncrementalSort, for example) 
>> and put it into the Plan node on-demand. So, the way I want to go is a 
>> Plan::extlist node and create_plan hook, which may allow copying 
>> best_path data to the final plan. So, here, we will add a new 
>> parameter and avoid touching the core code.
>> But I would give +1 to current approach if it were done in a shorter 
>> time.
> 
> 
> Then before proceeding further, I think we need to benchmark this change 
> to ensure there are no performance regressions. If performance issues 
> arise, then using extensible EXPLAIN might be the only viable approach.
> 
> I have addressed most of the review comments except for the 
> ExplainPropertyText change. I am attaching v3 of the patch with these 
> updates. If anyone notices any performance issues, please let me know. 
> Issue with ExplainPropertyText for this thread I'll fix in the next 
> patches if it will be necessary.
> 
> So far, I have tested it on small machines. There are no performance 
> issues yet. I'll run benchmarks on larger ones soon.
I have some doubts here.
The number of distinct values says something only when it has taken 
together with the number of calls.
Frequently, one of the caching keys is from outer table A (10 tuples), 
and another is from outer table B (100 tuples). Calculating the number 
of successful cache fetches predicted by the planner may not be evident 
in the case of a composite cache key.

What I may propose here is:
1. Use fraction of calls, for example - 50% duplicated key values.
2. Show the calculated hit and eviction ratio.

The second option looks better to me. It is pretty understandable by a 
user and may be compared to the numbers obtained during the execution.

-- 
regards, Andrei Lepikhov