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

On Mon, Mar 24, 2025 at 3:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> FWIW, I share these doubts about whether these values are useful
> enough to include in the default EXPLAIN output. My main beef
> with them though is that they are basically numbers derived along
> the way to producing a cost estimate, and I don't think we break
> out such intermediate results for other node types.
>

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.

Put differently, the reason this should be in the default EXPLAIN output
(or at least the VERBOSE output), is because Memoize's impact on costing is
counterintuitive (in my experience), and breaks the user's understanding of
planner costs you can usually derive by looking at the per-node cost
details, which typically flows up (i.e. gets larger as you step higher up
in the tree).

>
> It's looking like Robert's "pg_overexplain" will hit the tree soon,
> so maybe there could be a case for teaching that to emit additional
> costing details such as these?
>

I don't think that addresses the end-user usability sufficiently - from
what I gathered "pg_overexplain" was meant for a hacker audience, not
DBAs/etc interpreting Postgres plan choices.

Thanks,
Lukas

--
Lukas Fittl