public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Ilia Evdokimov <[email protected]>
To: David Rowley <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
Date: Thu, 20 Mar 2025 19:54:07 +0100
Message-ID: <[email protected]> (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]>
On 20/3/2025 15:03, Ilia Evdokimov wrote:
>
> On 20.03.2025 15:32, Andrei Lepikhov wrote:
>> I quite frequently need the number of distinct values (or groups)
>> predicted during the Memoize node creation to understand why caching
>> is sometimes employed or not.
>> But I had thought about an alternative way: having an extensible
>> EXPLAIN (thanks to Robert), we may save optimisation-stage data (I
>> have the same necessity in the case of IncrementalSort, for example)
>> and put it into the Plan node on-demand. So, the way I want to go is a
>> Plan::extlist node and create_plan hook, which may allow copying
>> best_path data to the final plan. So, here, we will add a new
>> parameter and avoid touching the core code.
>> But I would give +1 to current approach if it were done in a shorter
>> time.
>
>
> Then before proceeding further, I think we need to benchmark this change
> to ensure there are no performance regressions. If performance issues
> arise, then using extensible EXPLAIN might be the only viable approach.
>
> I have addressed most of the review comments except for the
> ExplainPropertyText change. I am attaching v3 of the patch with these
> updates. If anyone notices any performance issues, please let me know.
> Issue with ExplainPropertyText for this thread I'll fix in the next
> patches if it will be necessary.
>
> So far, I have tested it on small machines. There are no performance
> issues yet. I'll run benchmarks on larger ones soon.
I have some doubts here.
The number of distinct values says something only when it has taken
together with the number of calls.
Frequently, one of the caching keys is from outer table A (10 tuples),
and another is from outer table B (100 tuples). Calculating the number
of successful cache fetches predicted by the planner may not be evident
in the case of a composite cache key.
What I may propose here is:
1. Use fraction of calls, for example - 50% duplicated key values.
2. Show the calculated hit and eviction ratio.
The second option looks better to me. It is pretty understandable by a
user and may be compared to the numbers obtained during the execution.
--
regards, Andrei Lepikhov
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]
Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment
In-Reply-To: <[email protected]>
* 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