Hello,
I just implemented a small change that adds another column “mem_usage” to the system view “pg_prepared_statements”. It returns the memory usage total of CachedPlanSource.context, CachedPlanSource.query_content and if available CachedPlanSource.gplan.context.
Looks like this:
IKOffice_Daume=# prepare test as select * from vw_report_salesinvoice where salesinvoice_id = $1;
PREPARE
IKOffice_Daume=# select * from pg_prepared_statements;
name | statement | prepare_time | parameter_types | from_sql | mem_usage
------+----------------------------------------------------------------------------------+------------------------------+-----------------+----------+-----------
test | prepare test as select * from vw_report_salesinvoice where salesinvoice_id = $1; | 2019-07-27 20:21:12.63093+02 | {integer} | t | 33580232
(1 row)
I did this in preparation of reducing the memory usage of prepared statements and believe that this gives client application an option to investigate which prepared statements should be dropped. Also this makes it possible to directly examine the results of further changes and their effectiveness on reducing the memory load of prepared_statements.
Is a patch welcome or is this feature not of interest?
Also I wonder why the “prepare test as” is part of the statement column. I isn’t even part of the real statement that is prepared as far as I would assume. Would prefer to just have the “select *…” in that column.
Kind regards,
Daniel Migowski
