Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment

On 3/24/25 23:45, Tom Lane wrote:
> Lukas Fittl <lukas(at)fittl(dot)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