Message-ID: <44229379-3901-4cb0-8812-b354ef70d5e2@gmail.com>
Date: Thu, 20 Mar 2025 13:32:41 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
To: David Rowley <dgrowleyml@gmail.com>,
 Ilia Evdokimov <ilya.evdokimov@tantorlabs.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>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAApHDvpq2gOt3gnzkd4Ud=wrT7YRGrF3zb29jgWzDsYEhETrOA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/44229379-3901-4cb0-8812-b354ef70d5e2%40gmail.com>
Precedence: bulk

On 20/3/2025 11:37, David Rowley wrote:
> I'm also slightly concerned about making struct Memoize bigger. I had
> issues with a performance regression [1] for 908a96861 when increasing
> the WindowAgg struct size last year and the only way I found to make
> it go away was to shuffle the fields around so that the struct size
> didn't increase. I think we'll need to see a benchmark of a query that
> hits Memoize quite hard with a small cache size to see if the
> performance decreases as a result of adding the ndistinct field. It's
> unfortunate that we'll not have the luxury of squeezing this double
> into padding if we do see a slowdown.
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.

-- 
regards, Andrei Lepikhov