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

From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
To: David Rowley <dgrowleyml(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Lukas Fittl <lukas(at)fittl(dot)com>, Daniel Gustafsson <daniel(at)yesql(dot)se>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>, Andrei Lepikhov <lepihov(at)gmail(dot)com>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
Date: 2025-04-02 09:35:33
Message-ID: 7c7f40ae-dc26-4191-af3f-3277ab1c45a2@tantorlabs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


On 02.04.2025 00:06, David Rowley wrote:
> I tried to move things along to address Tom's concern about not
> wanting to show this in EXPLAIN's standard output. I suggested in [1]
> that we could use EXPLAIN ANALYZE, but nobody commented on that.
> EXPLAIN ANALYZE is much more verbose than EXPLAIN already, and if we
> put these in EXPLAIN ANALYZE then the viewer can more easily compare
> planned vs actual. I had mentioned that Hash Join does something like
> this for buckets.
>
> David
>
> [1]https://postgr.es/m/CAApHDvqPkWmz1Lq23c9CD+mAW=hgPrD289tC30f3H1f6Ng59+g@mail.gmail.com

Apologies for not addressing your earlier suggestion properly. After
reconsidering, I agree that showing ndistinct and est_entries in EXPLAIN
ANALYZE when there are actual cache misses is the best approach. This is
typically when users notice performance regressions and start
investigating the cause, so surfacing planner expectations at that point
can be the most useful. I attached v6 patch with changes.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

Attachment Content-Type Size
v6-0001-Display-estimated-distinct-keys-and-cache-capacity.patch text/x-patch 5.7 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message David Geier 2025-04-02 09:44:28 Re: Hashed IN only applied to first encountered IN
Previous Message Amit Kapila 2025-04-02 09:34:07 Re: Fix 035_standby_logical_decoding.pl race conditions