pg_stat_statements

While in many cases you might still want to save full logs, the pg_stat_statements feature added in PostgreSQL 8.4 can substitute as a way to analyze queries without needing an intermediate logging facility. The feature is documented at http://www.postgresql.org/docs/current/static/pgstatstatements.html.

This is a contrib module, not necessarily installed by default. See the discussion of the contrib modules in Chapter 1, PostgreSQL Versions. Like pg_buffercache, it needs to be installed in each database you use it against. Here's an example of installing it on a system with the RedHat Linux directory layout for PostgreSQL:

    $ psql -d pgbench -f 
    /usr/share/postgresql/contrib/contrib/pg_stat_statements.sql

You enable this using postgresql.conf additions like these:

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

This is then followed by fully restarting the server. Once you've run some queries and therefore populated the information in pg_stat_statements, you can look at them the same as any other view:

    pgbench=# SELECT round(total_time*1000)/1000 AS total_time,query 
      FROM pg_stat_statements ORDER BY total_time DESC;
    total_time | query                            
      78.104 | UPDATE pgbench_accounts SET abalance = abalance + $1
    WHERE aid = $2;
      1.826 | UPDATE pgbench_branches SET bbalance = bbalance + $1
    WHERE bid = $2;
      0.619 | UPDATE pgbench_tellers SET tbalance = tbalance + $1
    WHERE tid = $2;
     0.188 | INSERT INTO pgbench_history (tid, bid, aid, delta,
     mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);
     0.174 | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
     0.026 | vacuum pgbench_branches
     0.015 | vacuum pgbench_tellers
     0.011 | BEGIN;
     0.011 | END;
     0.001 | truncate pgbench_history  

It's extremely handy to have this facility built into the database, without needing external tools. There are a few limitations. No query fingerprint normalization can be done if you don't use query parameters--the preceding example required running pgbench in its prepared statement mode. The number of saved messages has a hard limit on it. It's easy to lose an interesting statement log as new activity occurs on the server.

And not necessarily every operation you can see in the logs will appear here. For example, note that in the preceding log from a standard pgbench test, timing for the UPDATE statements is listed, but the commits from the END statements are not accumulating a lot of time. A very different view of this information shows up if you look at the log file data, as shown in the next section.

PostgreSQL 9.0 adds an additional ability to pg_stat_statements. It can now also track the amount of database cache buffers used while processing a statement.