CYBERTEC PostgreSQL Logo

pg_stat_statements: The way I like it

10.2015 / Category: / Tags: |

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:

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:

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.

0 0 votes
Article Rating
Subscribe
Notify of
guest
9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mayoo N
Mayoo N
6 years ago

Thanks for this !!! Now, do we have to restart the Postgres to reset the queries ?

tseb
tseb
4 years ago
Reply to  Mayoo N

You can use pg_stat_statements_reset()

exadata
exadata
1 year ago

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;

Pavlo Golub
Pavlo Golub
1 year ago
Reply to  exadata

Thanks. updated

postgreNewbei
postgreNewbei
2 years ago

query should be updated, because there is no total_time or mean_time instead there is total_exec_time and mean_exec_time

Pavlo Golub
Pavlo Golub
1 year ago
Reply to  postgreNewbei

thanks updated

Robert Koltai
Robert Koltai
2 years ago

Hello

I do not understand how total_time became CPU percentage.
Can you please shed light on this?

eschraderMB
eschraderMB
3 years ago

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?

Michael Steinmann
Michael Steinmann
3 years ago
Reply to  eschraderMB

You have to use an account with superuser privileges, like the user postgres, to see those queries.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram