Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment - Mailing list pgsql-hackers

From Lukas Fittl
Subject Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
Date
Msg-id CAP53Pky44MuEYPDFcqR+ogffnifbuXkAWi5TvjhHmpSdj88o8w@mail.gmail.com
Whole thread Raw
In response to Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
List pgsql-hackers
On Mon, Mar 24, 2025 at 3:15 PM Tom Lane <tgl@sss.pgh.pa.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

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
Next
From: Tom Lane
Date:
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment