Closed
Description
When using PGSM with query_plan enabled, we can verify it only tracks the query execution plan for SELECT statements
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-# 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 [%]
--------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------+-----------------------+-----------------+---------------------
543586 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 | | 14709314.00 | 6105878.00 | 41.51
543586 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 | | 14709314.00 | 2765381.00 | 18.80
543586 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 | | 14709314.00 | 2737817.00 | 18.61
1 | vacuum analyze pgbench_accounts | | 14709314.00 | 821952.00 | 5.59
543585 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP) | | 14709314.00 | 562217.00 | 3.82
5000 | insert into pgbench_tellers(tid,bid,tbalance) values ($1,$2,$3) | | 14709314.00 | 5051.00 | 0.03
1 | alter table pgbench_accounts add primary key (aid) | | 14709314.00 | 2220.00 | 0.02
1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+| 14709314.00 | 6.00 | 0.00
| | Index Cond: (aid = 1389835) | | |
1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+| 14709314.00 | 6.00 | 0.00
| | Index Cond: (aid = 10119771) | | |
1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 | Index Scan using pgbench_accounts_pkey on pgbench_accounts+| 14709314.00 | 6.00 | 0.00
| | Index Cond: (aid = 30365540) | | |
(10 rows)
I personally consider PGSM to be an outstanding feature when query_plan is enabled. However, if it ONLY tracks the SELECT statements, its light considerably decreases.
I would like to get the chance to track the query plan for every statement, if possible.
Metadata
Metadata
Assignees
Labels
No labels