If this is something you want to start looking at, This script has been used for about 90% of my database performance troubleshooting. I set my minimum number of calls (the 500 below) and uncomment which column I want to query based on.
time_per is most common, but rows_per can tell you when people have crappy filters in their code (ie, no where clause) and total calls can let you focus on the ones with the biggest improvement.
Basically a quick/dirty top 20 list of your slowest, or biggest, or hardest on the cpu queries.
SELECT query
, calls
, total_time
, total_time / calls as time_per
, stddev_time
, rows
, rows / calls as rows_per
,100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
WHERE query not similar to '%pg_%'
and calls > 500
--ORDER BY calls
--ORDER BY total_time
order by time_per
--ORDER BY rows_per
DESC LIMIT 20;
This sounds great! I'm no expert but it seems that the postgres documentation disagrees about the process for enabling the extension -- "The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module."[1]
What I was really looking for was a ballpark estimate or guideline of what additional resources a server would need to run this extension.
It admittedly does vary based on the way you installed Postgres. Not all, but many installation methods already come with it in the shared_preload_libraries. You still have to run create extension for it to exist or it has to be enabled by the superuser. Postgres.app for example on the mac already has it there but you still have to run create extension.
time_per is most common, but rows_per can tell you when people have crappy filters in their code (ie, no where clause) and total calls can let you focus on the ones with the biggest improvement.
Basically a quick/dirty top 20 list of your slowest, or biggest, or hardest on the cpu queries.
What I was really looking for was a ballpark estimate or guideline of what additional resources a server would need to run this extension.
[1]https://www.postgresql.org/docs/11/pgstatstatements.html