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
orderto 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
currentlimited 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
verymuch assuming that the context will be free'd fully after some time anyway. In the case of PreparedStatements that
willbe 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.
Detectingsuch duplicated references would involve sweeping over a plan after it has been created, placing all stuff
intosome kind of map and unifying the references to those definitions. The time spend for this might even speed up
executingthe query because less memory has to be touched when the plan is executed potentially leading to better cpu
cachehit 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
thisstuff in the connection process and just link to entries in it. I assume that when the structure of datatypes
changeson the server the plans have to be discarded anyway and you already know somehow which plans have to be
discardedand use what structures, so there already might be such a dictionary in place? If now, it might also increase
thespeed of dropping stuff when adding a refcount to these structures in the dictionary, so knowing if it has been
referencedsomewhere.  

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

Regards,
Daniel Migowski


-----Ursprüngliche Nachricht-----
Von: Daniel Migowski
Gesendet: Donnerstag, 25. Juli 2019 00:32
An: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.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
handlefor 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
slightlyless memory hungry?  

Regards and many thanks ahead,
Daniel Migowski

...