Potential memory waste

[dutow]

When using PGSM and query_plan enabled, we record the query plan strings, including the bind variable values on them. As a result of that, we can store several thousands of "different" execution plans when they are actually the same (E.g. when the bind variable value is a PK or UK).

I am guessing this might be leading to a huge memory waste.

postgres=# SELECT Sum(calls)                                AS calls,
postgres-#        query :: VARCHAR(100)                     AS QUERY,
postgres-#        query_plan :: VARCHAR(500)                AS query_plan,
postgres-#        (SELECT Round(Sum(shared_blks_hit) :: DECIMAL, 2)
postgres(#         FROM   pg_stat_monitor)                  AS total_shared_blks_hit,
postgres-#        Round(Sum(shared_blks_hit) :: DECIMAL, 2) AS shared_blks_hit,
postgres-#        Round(( ( Sum(shared_blks_hit) ) * 100 ) / (SELECT Sum(shared_blks_hit)
postgres(#                                                    FROM   pg_stat_monitor) ::
postgres(#              DECIMAL,
postgres(#        2)                                        AS "shared_blks_hit [%]"
postgres-# FROM   pg_stat_monitor
postgres-# WHERE  query like 'SELECT abalance FROM pgbench_accounts WHERE%'
postgres-# GROUP  BY query,
postgres-#           query_plan
postgres-# ORDER  BY "shared_blks_hit [%]" DESC
postgres-# LIMIT  10;
 calls |                        query                         |                         query_plan                         | total_shared_blks_hit | shared_blks_hit | shared_blks_hit [%]
-------+------------------------------------------------------+------------------------------------------------------------+-----------------------+-----------------+---------------------
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10000378)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 1000064)                              |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10000677)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10000801)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10000943)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10000968)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10001280)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10001602)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10002120)                             |                       |                 |
     1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+|            5821495.00 |            5.00 |                0.00
       |                                                      |   Index Cond: (aid = 10000054)                             |                       |                 |
(10 rows)

Please, consider working on this case at the same time as PG-630.

The intention is NOT to remove the chance of tracking the bind variable values since it is a powerful tool when troubleshooting unstable execution plans based on heterogenous cardinality. Instead of removing it it would be better to get a flag that enables or disables the bind variable data collection, similar to we are doing with pg_stat_monitor.pgsm_normalized_query

https://jira.percona.com/browse/PG-631

[ImTheKai]

Will be tracked here https://jira.percona.com/browse/PG-631