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 need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou 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()
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
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
Hello
I do not understand how total_time became CPU percentage.
Can you please shed light on this?
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.