Re: Speedup truncations of temporary relation forks

Hi,

On Sat, May 31, 2025 at 7:49 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Fri, May 30, 2025 at 06:01:16PM +0700, Daniil Davydov wrote:
> > For now we fully scan local buffers for each fork of the temporary
> > relation that we want to truncate (in order to drop its buffers). It
> > happens in the function "DropRelationBuffers".
> > There used to be the same problem for regular tables (i.e. shared
> > buffers) and it was fixed in commit [1] and now shared buffers are
> > scanned only one time for those three relation forks.
> > I suggest making the same fix for temporary relations. See attached patch.
>
> Applying the same kind of optimization for local buffers makes sense
> here, even if the impact is more limited than regular relations.
>

Thanks for looking into it!

> > BTW, I see that we call "DropRelationBuffers" separately for relation,
> > toast table and indexes. What if we collect all this information in
> > advance and iterate over the local/shared buffers only once?
> > I understand that it will look kinda ugly, but it will increase
> > performance for sure.
>
> I guess it does.  Do you have numbers to share with a test case?
>

Not yet. I proceed from the assumption that if the temp_buffers
parameter is set to a large value (some users set it to more than a
gigabyte), then the vast majority of time is spent iterating through
the local buffers.
Thus, if we reduce the number of iterations from N to (for example)
N/10, we can get a 10x increase in performance. Of course, this is a
super rough assumption, but I think you understand my point.
In the near future I will prepare a patch for the idea above and try
to do some measurements. If there is a significant difference, I will
definitely let you know.

Anyway, first I suggest committing the current patch.

> Please make sure to add this patch to the next commit fest.

OK, already created.

--
Best regards,
Daniil Davydov