As a reminder, if you ended up on this page by looking up on bing
pg_stat_statements, first I’m impressed, you get results with bing ? But also, this article is based on a talk that I did at the pgdayParis 2018 and here are the slides
What is pg_stat_statements ?
pg_stat_statement is a postgreSQL extension, once you enable it, it tracks statistics on the queries executed by a server. It will help you find slow queries.
You can use it in your local environment but also on production, if you are afraid of the performances loss, here is an article on the subject.
How can I activate it ?
First go in your psql:
$ psql -U owl -d owl_conference
And create the extension. Only superuser or database owner can create extensions. So you might need to check the privileges of the user you are connecting with.
CREATE EXTENSION pg_stat_statements;
You then have to change your
postgresql.conf file and restart. You can’t only reload your conf file because pg_stat_statements needs to be added to the
shared_preload_libraries. So here is the configuration.
shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all
You can change the
pg_stat_statements.max if you want to track more queries or are afraid of the size of the table.
And now what?
Well, now you can simply find statistics about the queries tracked by running
SELECT total_time, min_time, max_time, mean_time, calls, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 100;
You will get a set of results looking like:
-[ RECORD 1 ]--------------------------------------------------------- total_time | 643.624 min_time | 54.424 max_time | 531.417 mean_time | 214.541333333333 calls | 3 query | SELECT COUNT(*) FROM letters;
So from that you understand:
- The query has been executed 3 times (
- The execution time was between 54 and 531 ms (
total_timeis the sum of the time of each execution
Well that’s it ! But now that you know which queries are slow, you probably want to understand what’s wrong. To do that, I encourage you to use EXPLAIN, if you want to read about it, I am currently writing articles on
EXPLAIN, so be patient, they should be available in the next few days :)