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
