If you really want to track down slow queries, massive I/O and lousy performance, there is no way around the pg_stat_statements extension.
However, the pg_stat_statements system view is full of information and many people get lost. Therefore it can make sense, to come up with a clever query to provide administrators with really relevant information. Without finding the really relevant information, tuning is somewhat pointless.
Here is my personal favorite query to track down slow queries:
|
1 2 3 4 5 6 7 8 9 |
test=# SELECT substring(query, 1, 50) AS short_query, round(total_exec_time::numeric, 2) AS total_exec_time, calls, round(mean_exec_time::numeric, 2) AS mean, round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; |
The output contains a short version of the query (this can be handy if you are using text terminal as I do). Then there is the tota_execl_time of the query in the first column along with the number of calls and the mean execution time.
Personally I have found it useful to calculate an overall percentage for each query. It helps me to get a feeling of what lies in stock for me in case I can manage to optimize a certain query. To me the percentage value provides me with relevance because it is pretty pointless to work on queries, which only need 0.5% of time:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
short_query | total_exec_time | calls | mean | percentage_cpu ----------------------------------------------------+-----------------+--------+--------+---------------- UPDATE pgbench_tellers SET tbalance = tbalance + ? | 585005.76 | 143881 | 4.07 | 54.03 UPDATE pgbench_branches SET bbalance = bbalance + | 481968.27 | 143881 | 3.35 | 44.51 UPDATE pgbench_accounts SET abalance = abalance + | 9801.43 | 143881 | 0.07 | 0.91 SELECT abalance FROM pgbench_accounts WHERE aid = | 2928.61 | 143881 | 0.02 | 0.27 INSERT INTO pgbench_history (tid, bid, aid, delta, | 2236.59 | 143881 | 0.02 | 0.21 copy pgbench_accounts from stdin | 371.97 | 1 | 371.97 | 0.03 vacuum analyze pgbench_accounts | 128.22 | 1 | 128.22 | 0.01 BEGIN; | 108.18 | 143881 | 0.00 | 0.01 END; | 88.91 | 143881 | 0.00 | 0.01 alter table pgbench_accounts add primary key (aid) | 74.88 | 1 | 74.88 | 0.01 vacuum analyze pgbench_branches | 14.99 | 1 | 14.99 | 0.00 vacuum pgbench_branches | 11.57 | 1 | 11.57 | 0.00 create table pgbench_history(tid int,bid int,aid | 4.36 | 1 | 4.36 | 0.00 vacuum analyze pgbench_tellers | 2.28 | 1 | 2.28 | 0.00 alter table pgbench_branches add primary key (bid) | 2.03 | 1 | 2.03 | 0.00 SELECT substring(query, ?, ?) AS short_query, | 1.93 | 2 | 0.96 | 0.00 SELECT substring(query, ?, ?) AS short_query, | 1.82 | 1 | 1.82 | 0.00 SELECT a.attname, + | 1.39 | 2 | 0.69 | 0.00 pg_catalog.format_type(a.attty | | | | alter table pgbench_tellers add primary key (tid) | 1.07 | 1 | 1.07 | 0.00 vacuum analyze pgbench_history | 1.02 | 1 | 1.02 | 0.00 (20 rows) |
Working top down is usually a good idea.
Of course everybody will have his own ideas of how to approach the problem and the information provided by the query is not sufficient to fully optimize a system. However, I have found it useful to gain a quick overview of what is going on.
See also my blog about speeding up slow queries.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Thanks for this !!! Now, do we have to restart the Postgres to reset the queries ?
You can use pg_stat_statements_reset()
In Azure Postgres SQL, I get for the short_query field on most of the rows, except for a few python scripts I used to create the tables. Any tips on seeing the queries?
You have to use an account with superuser privileges, like the user postgres, to see those queries.
Hello
I do not understand how total_time became CPU percentage.
Can you please shed light on this?
query should be updated, because there is no total_time or mean_time instead there is total_exec_time and mean_exec_time
thanks updated
please find the updated query for postgres-14
select substring(query,1, 50) AS short_query,
round(total_exec_time::numeric, 2) AS total_exec_time,
calls,round(min_exec_time::numeric,2) AS mean,
round((100*total_exec_time/ sum(total_exec_time::numeric)
OVER())::numeric,2) AS percentage_overall
from pg_stat_statements
order by total_exec_time desc
LIMIT 20;
Thanks. updated