PostgreSQL pg_stat_statements Extension

pg_stat_statements is a module that needs to be loaded and is not available in the default configuration. Loading it is quite easy. Create the extension as usual:

postgres@oel7:/home/postgres/ [PG6] sqh
Null display is "NULL".
Timing is on.
psql (9.5alpha2)
Type "help" for help.
 
(postgres@[local]:4448) [postgres] > create extension pg_stat_statements;
CREATE EXTENSION
Time: 281.765 ms
(postgres@[local]:4448) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.1     | public     | support for indexing common datatypes in GiST
 pg_stat_statements | 1.3     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(4 rows)

After the extension is available we need to adjust the shared_preload_libraries parameter:

(postgres@[local]:4448) [postgres] > show shared_preload_libraries;
 shared_preload_libraries 
--------------------------
  
(1 row)
 
(postgres@[local]:4448) [postgres] > alter system set shared_preload_libraries='pg_stat_statements';
ALTER SYSTEM
Time: 55.005 ms
 
(postgres@[local]:4448) [postgres] > select name,pending_restart 
                                       from pg_settings 
                                      where name in ('shared_preload_libraries');
           name           | pending_restart 
--------------------------+-----------------
 shared_preload_libraries | f
(1 row)
 
Time: 1.517 ms
(postgres@[local]:4448) [postgres] > select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Basically pg_stat_statements can be used from now own. But there are some parameters to look at if you want to fine tune. Check the documentation for the description.

(postgres@[local]:4448) [postgres] > show pg_stat_statements.max;
-[ RECORD 1 ]----------+-----
pg_stat_statements.max | 5000
 
Time: 0.230 ms
(postgres@[local]:4448) [postgres] > show pg_stat_statements.track;
-[ RECORD 1 ]------------+----
pg_stat_statements.track | top
 
Time: 0.211 ms
(postgres@[local]:4448) [postgres] > show pg_stat_statements.track_utility;
-[ RECORD 1 ]--------------------+---
pg_stat_statements.track_utility | on
 
Time: 0.215 ms
(postgres@[local]:4448) [postgres] > show pg_stat_statements.save;
-[ RECORD 1 ]-----------+---
pg_stat_statements.save | on
 
Time: 0.212 ms

When we installed the extension a view was created with the following columns:

(postgres@[local]:4448) [postgres] > \d pg_stat_statements
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers 
---------------------+------------------+-----------
 userid              | oid              | 
 dbid                | oid              | 
 queryid             | bigint           | 
 query               | text             | 
 calls               | bigint           | 
 total_time          | double precision | 
 min_time            | double precision | 
 max_time            | double precision | 
 mean_time           | double precision | 
 stddev_time         | double precision | 
 rows                | bigint           | 
 shared_blks_hit     | bigint           | 
 shared_blks_read    | bigint           | 
 shared_blks_dirtied | bigint           | 
 shared_blks_written | bigint           | 
 local_blks_hit      | bigint           | 
 local_blks_read     | bigint           | 
 local_blks_dirtied  | bigint           | 
 local_blks_written  | bigint           | 
 temp_blks_read      | bigint           | 
 temp_blks_written   | bigint           | 
 blk_read_time       | double precision | 
 blk_write_time      | double precision | 

We can now query the view for information we are interested in, e.g.:

(postgres@[local]:4448) [postgres] > \x
Expanded display is on.
(postgres@[local]:4448) [postgres] > select userid,query,calls,total_time from pg_stat_statements;
-[ RECORD 1 ]
userid     | 10
query      | alter system set logging_collector=on;
calls      | 1
total_time | 30.13
-[ RECORD 2 ]
userid     | 10
query      | create extension pg_stat_statements;
calls      | 2
total_time | 250.54
-[ RECORD 3 ]
userid     | 10
query      | select name,pending_restart from pg_settings where name in (?,?,?,?,?);
calls      | 1
total_time | 0.627
-[ RECORD 4 ]
userid     | 10
query      | show log_rotation_size;
calls      | 1
total_time | 0.006

Additionally we can call a function which is named exactly the same as the view:

(postgres@[local]:4448) [postgres] > select * from pg_stat_statements(true);
-[ RECORD 1 ]
userid              | 10
dbid                | 13295
queryid             | 780340104
query               | alter system set logging_collector=on;
calls               | 1
total_time          | 30.13
min_time            | 30.13
max_time            | 30.13
mean_time           | 30.13
stddev_time         | 0
rows                | 0
shared_blks_hit     | 0
shared_blks_read    | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
-[ RECORD 2 ]
userid              | 10
dbid                | 13295
queryid             | 1392856018
query               | create extension pg_stat_statements;
calls               | 2
total_time          | 250.54
min_time            | 1.489
max_time            | 249.051
mean_time           | 125.27
stddev_time         | 123.781
rows                | 0
shared_blks_hit     | 1150
shared_blks_read    | 90
Time: 0.742 ms

On top of either the view or the function we can now start to troubleshoot issues with the queries the server executes. Hope this helps.