AW: BUG #15923: Prepared statements take way too much memory.

I am considering trying PostgreSQL hacking to better understand the reasons of the memory consumption of Query Plans in order to be able to maybe reduce the memory used by them.

Given the current implementation and what I learned from reading about memory allocation in the backend I, in my current limited understanding of the backend, hope to find something like:

* MemoryContext's in which stuff is palloc'ed that is not really used anymore. I have read that usually you don't pfree very much assuming that the context will be free'd fully after some time anyway. In the case of PreparedStatements that will be much later than the devs of the Rewriter/Planner had in mind.
* Alternate plans that will never be used anymore but which's allocated memory is still not freed.
* References to data like table and datatype definitions that are copied into the plan but are also copied multiple times. Doesn't matter for small plans, but maybe for large ones where different subselects query the same tables. Detecting such duplicated references would involve sweeping over a plan after it has been created, placing all stuff into some kind of map and unifying the references to those definitions. The time spend for this might even speed up executing the query because less memory has to be touched when the plan is executed potentially leading to better cpu cache hit rates. This seems the most promising to me.
* Or maybe one could, if my previous assumption is true, instead of copying stuff to the plan build a dictionary of this stuff in the connection process and just link to entries in it. I assume that when the structure of datatypes changes on the server the plans have to be discarded anyway and you already know somehow which plans have to be discarded and use what structures, so there already might be such a dictionary in place? If now, it might also increase the speed of dropping stuff when adding a refcount to these structures in the dictionary, so knowing if it has been referenced somewhere.

Because you know the code better, do you think it might be worthwhile to go for these things or would it be waste of time in your eyes?

Regards,
Daniel Migowski

-----Ursprüngliche Nachricht-----
Von: Daniel Migowski
Gesendet: Donnerstag, 25. Juli 2019 00:32
An: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Betreff: AW: BUG #15923: Prepared statements take way too much memory.

I never keep more that 250 Prepared Statements. Sadly, with 10 connections to the database pooled, this is too much to handle for my poor servers.

But really... 45MB? The query has 132 nodes, and each takes up 300kb memory? Do you store the table contents in that thing? I don't want to be greedy, but might it be possible to store your Prepared Statements in a data structure that is slightly less memory hungry?

Regards and many thanks ahead,
Daniel Migowski

...