Re: strange slow query - lost lot of time somewhere

On Mon, May 2, 2022 at 7:13 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 3 May 2022 at 11:02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > > On Mon, 2 May 2022 at 21:00, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > >> I found a query that is significantly slower with more memory
> >
> > > If it was work_mem you increased, it seems strange that the plan would
> > > switch over to using a Nested Loop / Memoize plan.
> >
> > Yeah, there's something unexplained there.
> >
> > I think that the most probable explanation for the symptoms is that
> > cost_memoize_rescan is computing some insane value for est_entries,
> > causing ExecInitMemoize to allocate-and-zero a huge hash table,
> > which ExecEndMemoize then frees again. Neither of those steps
> > gets counted into any plan node's runtime, but EXPLAIN's total
> > execution time will include them. An insane value for est_entries
> > could perhaps go along with a cost misestimate that convinces the
> > planner to include the memoize even though it seems pointless.
>
> That seems pretty unlikely to me. est_entries is based on the minimum
> value of the expected number of total cache entries and the ndistinct
> value. ndistinct cannot be insane here as ndistinct is never going to
> be higher than the number of calls, which is the row estimate from the
> outer side of the join. That's 91 in both cases here. As far as I
> can see, that's just going to make a table of 128 buckets.
>

If est_entries goes to zero due to hash_mem_bytes/est_entry_bytes < 1
(hence floor takes it to zero) the executor will use a size value of 1024
instead in build_hash_table.

That seems unlikely but there is no data to support or refute it.

> I'm open to making improvements to the comments in that area. I do
> remember spending quite a bit of time trying to make things as clear
> as possible as it is fairly complex what's going on there.
>
>
A few more intermediate calculation variables, along with descriptions,
would help.

e.g., min(est_cache_entries, ndistinct) is repeated twice after its initial
definition.

retention_ratio per my other reply

The (ndistinct/calls) part of hit_ratio being described specifically.

David J.