On Thu, 2 Mar 2023 19:53:14 +0100
Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
> On 3/2/23 19:15, Jehan-Guillaume de Rorthais wrote:
...
> > There was some thoughts about how to make a better usage of the memory. As
> > memory is exploding way beyond work_mem, at least, avoid to waste it with
> > too many buffers of BufFile. So you expand either the work_mem or the
> > number of batch, depending on what move is smarter. TJis is explained and
> > tested here:
> >
> > https://www.postgresql.org/message-id/20190421161434.4hedytsadpbnglgk%40development
> > https://www.postgresql.org/message-id/20190422030927.3huxq7gghms4kmf4%40development
> >
> > And then, another patch to overflow each batch to a dedicated temp file and
> > stay inside work_mem (v4-per-slice-overflow-file.patch):
> >
> > https://www.postgresql.org/message-id/20190428141901.5dsbge2ka3rxmpk6%40development
> >
> > Then, nothing more on the discussion about this last patch. So I guess it
> > just went cold.
>
> I think a contributing factor was that the OP did not respond for a
> couple months, so the thread went cold.
>
> > For what it worth, these two patches seems really interesting to me. Do you
> > need any help to revive it?
>
> I think another reason why that thread went nowhere were some that we've
> been exploring a different (and likely better) approach to fix this by
> falling back to a nested loop for the "problematic" batches.
>
> As proposed in this thread:
>
> https://www.postgresql.org/message-id/20190421161434.4hedytsadpbnglgk%40development
Unless I'm wrong, you are linking to the same «frustrated as heck!» discussion,
for your patch v2-0001-account-for-size-of-BatchFile-structure-in-hashJo.patch
(balancing between increasing batches *and* work_mem).
No sign of turning "problematic" batches to nested loop. Did I miss something?
Do you have a link close to your hand about such algo/patch test by any chance?
> I was hoping we'd solve this by the BNL, but if we didn't get that in 4
> years, maybe we shouldn't stall and get at least an imperfect stop-gap
> solution ...
I'll keep searching tomorrow about existing BLN discussions (is it block level
nested loops?).
Regards,
