public inbox for [email protected]  

help / color / mirror / Atom feed
From: Lukas Fittl <[email protected]>
To: Tom Lane <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: Ilia Evdokimov <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
Date: Mon, 24 Mar 2025 15:30:03 -0700
Message-ID: <CAP53Pky44MuEYPDFcqR+ogffnifbuXkAWi5TvjhHmpSdj88o8w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAP53Pky29GWAVVk3oBgKBDqhND0BRBN6yTPeguV_qSivFL5N_g@mail.gmail.com>
	<CAApHDvrBctjUFjY77BFd_MqPD+yf4nOZSKzbdzP70XntkknY8A@mail.gmail.com>
	<[email protected]>
	<CAP53Pkz-sK8En2c05rJDcV7pz8+OsbNqOnm_SO4tsuwm=n=3OQ@mail.gmail.com>
	<[email protected]>
	<CAApHDvpq2gOt3gnzkd4Ud=wrT7YRGrF3zb29jgWzDsYEhETrOA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAApHDvqHe15LL=K5j71qxLdPCaFFPeohtBe7=sK29Ord3ZVQcg@mail.gmail.com>
	<[email protected]>
	<CAApHDvoE5S5FkvEq+N3-J9LfaVUpWLOnczOYAOEvBMCY20=pdg@mail.gmail.com>
	<[email protected]>
	<CAApHDvr9kjuo0rX03Qm0cDP_tFNJ8crOtpRzbyS0Zwtg2BorRw@mail.gmail.com>
	<[email protected]>

On Mon, Mar 24, 2025 at 3:15 PM Tom Lane <[email protected]> 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


view thread (39+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
  In-Reply-To: <CAP53Pky44MuEYPDFcqR+ogffnifbuXkAWi5TvjhHmpSdj88o8w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox