Message-ID: <bf6710b9-5c38-48e2-9043-cced9e7e4082@gmail.com>
Date: Tue, 25 Mar 2025 10:32:52 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost
 adjustment
To: Tom Lane <tgl@sss.pgh.pa.us>, Lukas Fittl <lukas@fittl.com>
Cc: David Rowley <dgrowleyml@gmail.com>,
 Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 Daniel Gustafsson <daniel@yesql.se>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Nikolay Samokhvalov <samokhvalov@gmail.com>
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>
 <8e12aa55-39eb-4e03-a8f7-077fe02309c3@gmail.com>
 <CAApHDvqHe15LL=K5j71qxLdPCaFFPeohtBe7=sK29Ord3ZVQcg@mail.gmail.com>
 <ff747c1d-1925-45ca-9c47-2763be45cba0@gmail.com>
 <CAApHDvoE5S5FkvEq+N3-J9LfaVUpWLOnczOYAOEvBMCY20=pdg@mail.gmail.com>
 <11c709e1-9b17-4c00-9ead-93609a09b9db@gmail.com>
 <CAApHDvr9kjuo0rX03Qm0cDP_tFNJ8crOtpRzbyS0Zwtg2BorRw@mail.gmail.com>
 <1161645.1742854534@sss.pgh.pa.us>
 <CAP53Pky44MuEYPDFcqR+ogffnifbuXkAWi5TvjhHmpSdj88o8w@mail.gmail.com>
 <1164845.1742856354@sss.pgh.pa.us>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <1164845.1742856354@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/bf6710b9-5c38-48e2-9043-cced9e7e4082%40gmail.com>
Precedence: bulk

On 3/24/25 23:45, Tom Lane wrote:
> Lukas Fittl <lukas@fittl.com> writes:
>> The main argument I had initially when proposing this, is that Memoize is
>> different from other plan nodes, in that it makes the child node costs
>> "cheaper". Clearly seeing the expected cache hit/ratio (that drives that
>> costing modification) helps interpret why the planner came up with a given
>> plan.
> As I said, I'm not necessarily averse to showing these numbers
> somehow.  But I don't think they belong in the default output,
> and I'm not even convinced that VERBOSE is the right place.
> pg_overexplain seems like it could be an ideal home for this
> sort of detail.
I prefer not to see these numbers in the default EXPLAIN output, not 
only because they can fluctuate but mainly because I like to view the 
basic query structure without additional details.
As I mentioned earlier, most of the information we typically need to 
explore query plans stays in path nodes and does not transfer to the 
Plan node. I believe this should stay as is, as we deal with numerous 
cases and a vast amount of data.
It would be beneficial to expose extra data in an external extension. By 
implementing a `create_plan` hook and an extensible list node in both 
Path and Plan structures, we could provide a machinery for writing an 
extension that can expose any planning-stage information in EXPLAIN on 
demand.
This could encourage developers to create a "pg_extended_explain" 
extension, which would address most user needs without requiring 
additional changes to the core system.

-- 
regards, Andrei Lepikhov