ERROR: relation pg_stat_statements does not exist

When I try to select * from pg_stat_statements to get some information about queries that run on postgres database I getting

  
ERROR: relation pg_stat_statements does not exist

In this post I'll write step by step how to install pg_stat_statements in postgresql to analyze queries, whcih is very helpful during performance tuning. 

The pg_stat_statments table holds information on queries that ran in the past table has one row per for each query that ran. It provides useful information in columns like: 

pg_stat_statements.query — the query text

pg_stat_statements.calls — The number of times this query was run

pg_stat_statements.total_exec_time — The total time (ms) spent on the query.

Here is step by step how to install pg_stat_statements:

postgres=# select * from pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: select * from pg_stat_statements;
                      ^
postgres=# SELECT * FROM pg_available_extensions where name='pg_stat_statements';
        name        | default_version | installed_version |                                comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10            |                   | track planning and execution statistics of all SQL statements executed
(1 row)

Extensions in PostgreSQL server are database level. We have create extension for each database separately.

postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres=#
postgres=#
postgres=# SELECT * FROM pg_available_extensions where name='pg_stat_statements';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# SELECT * FROM pg_available_extensions where name='pg_stat_statements';
        name        | default_version | installed_version |                                comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10            | 1.10              | track planning and execution statistics of all SQL statements executed
(1 row)


postgres=# select * from pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
postgres=#

in postgresql.conf file add these records at the end:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

restart postgresql:

systemctl restart postgresql-15

now you can check pg_stat_statements and use it for performance tuning, for example this query returns top 10 statement for database by average elapsed time:

SELECT 
       query, 
       calls, 
       (total_exec_time/calls/1000)::integer AS avg_time_seconds 
FROM pg_stat_statements
WHERE 1=1 
--calls > 1000
ORDER BY avg_time_seconds DESC
LIMIT 10;

Happy postgres tuning.