Re: Trim the heap free memory

I wrote:
> The single test case you showed suggested that maybe we could
> usefully prod glibc to free memory at query completion, but we
> don't need all this interrupt infrastructure to do that.  I think
> we could likely get 95% of the benefit with about a five-line
> patch.

To try to quantify that a little, I wrote a very quick-n-dirty
patch to apply malloc_trim during finish_xact_command and log
the effects.  (I am not asserting this is the best place to
call malloc_trim; it's just one plausible possibility.)  Patch
attached, as well as statistics collected from a run of the
core regression tests followed by

grep malloc_trim postmaster.log | sed 's/.*LOG:/LOG:/' | sort -k4n | uniq -c >trim_savings.txt

We can see that out of about 43K test queries, 32K saved nothing
whatever, and in only four was more than a couple of meg saved.
That's pretty discouraging IMO.  It might be useful to look closer
at the behavior of those top four though.  I see them as

2024-09-15 14:58:06.146 EDT [960138] LOG:  malloc_trim saved 7228 kB
2024-09-15 14:58:06.146 EDT [960138] STATEMENT:  ALTER TABLE delete_test_table ADD PRIMARY KEY (a,b,c,d);

2024-09-15 14:58:09.861 EDT [960949] LOG:  malloc_trim saved 12488 kB
2024-09-15 14:58:09.861 EDT [960949] STATEMENT:  with recursive search_graph(f, t, label, is_cycle, path) as (
		select *, false, array[row(g.f, g.t)] from graph g
		union distinct
		select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)
		from graph g, search_graph sg
		where g.f = sg.t and not is_cycle
	)
	select * from search_graph;

2024-09-15 14:58:09.866 EDT [960949] LOG:  malloc_trim saved 12488 kB
2024-09-15 14:58:09.866 EDT [960949] STATEMENT:  with recursive search_graph(f, t, label) as (
		select * from graph g
		union distinct
		select g.*
		from graph g, search_graph sg
		where g.f = sg.t
	) cycle f, t set is_cycle to 'Y' default 'N' using path
	select * from search_graph;

2024-09-15 14:58:09.853 EDT [960949] LOG:  malloc_trim saved 12616 kB
2024-09-15 14:58:09.853 EDT [960949] STATEMENT:  with recursive search_graph(f, t, label) as (
		select * from graph0 g
		union distinct
		select g.*
		from graph0 g, search_graph sg
		where g.f = sg.t
	) search breadth first by f, t set seq
	select * from search_graph order by seq;

I don't understand why WITH RECURSIVE queries might be more prone
to leave non-garbage-collected memory behind than other queries,
but maybe that is worth looking into.

			regards, tom lane