📚 Install and Create and use pg_stat_statements extension¶

🔍 Prepare：¶

## OS and Software environment：

Environment name	Version
OS	Rocky Linux 9.x
DB	PostgreSQL 16.X

1. Install and create pg_stat_statements(postgres)¶

• 1.1 Check the pg_stat_statements.so exists in your library folder or not

locate pg_stat_statements.so

• 1.2 Add pg_stat_statements to the shared_preload_libraries in your postgresql.conf file, like so: shared_preload_libraries = 'pg_stat_statements'

vim /pgdata/pg16zekai/postgresql.conf

This parameter needs to restart DB to enable

• 1.3 Restart DB

pg_ctl restart -D /pgdata/pg16zekai/ -l logfile

• 1.4 Create pg_stat_statesment extension

psql -p 5435

CREATE EXTENSION pg_stat_statements;

Suggest Create pg_stat_statements Extension On Postgres !!! Because It will Collect the Execution Records of all DBs in the Cluster

• 1.5 You can see pg_stat_statements view has a ton of information on what queries are taking up the most time

\d pg_stat_statements

• 1.6 you also can sse the pgadmin to connect DB and you can see the pg_stat_statements extension is added (pg_stat_statements extension installed successfully)

• 1.7 You can get the queries that take up the most CPU time by running

SELECT total_exec_time,calls,min_exec_time,max_exec_time,mean_exec_time,query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Note: The total_exec_time column is the total amount of time spent running the query. You can think of it as average query time *number of times the query was ran Fields information total_exec_time = total execution time(average query time * number of times) query = the sql content calls= execute times min_exec_time= min costing time in this sql max_exec_time= max costing time in this sql mean_exec_time= average costing time in this sql

• 1.8 You can get the queries that take the longest on average by running

SELECT mean_exec_time, query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

• 1.9 you can reset the pg_stat_statements data by following command on pgadmin

  • 1.9.1 Before

  SELECT * FROM public.pg_stat_statements LIMIT 100;
  

  

  • 1.9.2 To reset the data

  SELECT pg_stat_statements_reset();
  

  

  • 1.9.3 After (data will be reset and recount again)

  SELECT * FROM public.pg_stat_statements LIMIT 100;