You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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.
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
The text was updated successfully, but these errors were encountered: