pg_stat_statements The pg_stat_statements module provides a means for tracing and analyzing SQL statements executed in a server. The module should be loaded through shared_preload_libraries because it requires additional shared memory. You might need to restart servers to enable the module. NOTE: It'd be better to to execute statements using prepared statements or extended protocol if you want to get a better report from the module. If simple protocol is used, statements that have differences only in the query parameters are not grouped in the report. The definitions of the columns exposed by the view are: Name Type References Description userid oid pg_authid.oid User who executed the statement dbid oid pg_class.oid Database in which the statement is executed query text Query text of the statement calls bigint Number of being executed total_time bigint Total time spent for the statement in milliseconds cpu_time bigint CPU time spent for the statement in milliseconds gets bigint Total buffer gets during execution reads bigint Total buffer reads during execution writes bigint Total buffer writes during execution local_reads bigint Total direct reads during execution, for example disk sorting and materializing local_writes bigint Total direct writes during execution, for example disk sorting and materializing rows bigint Number of rows retrieved or affected by the statement There is one row for each statement. Statements are grouped when they have same SQL text, are in the same database, and are executed by the same user. Because of security restriction, non-super users cannot see query strings executed by other users. pg_stat_statements_reset() returns bool pg_stat_statements_reset resets all of statement statistics. pg_stat_statements view will be empty. statistics.max_statements (integer) statistics.max_statements is the maximum number of statements tracked by the module. If variations of statements are larger than the value, statistics of the least used statement is discarded. The default value is 1000. The module requires addional shared memory about statistics.max_statements * track_activity_query_size. This parameter can only be set at server start. statistics.track_statements (boolean) statistics.max_statements enables or disables collection of statement statistics by the module. The default value is on. Only superusers can change this setting. NOTE: Shared memory allocated by the module is not released even if statistics collection is disabled. statistics.saved_file (string) statistics.saved_file specifies the name of saved file where the statement statistics are loaded or dumped. The default value is 'global/pg_stat_statements.dump'. If the value if an empty string, statistics are neither loaded nor dumped. This parameter can only be set in the postgresql.conf file or on the server command line. If you set the above configuration parameters, you also need to add 'statistics' entry to custom_variable_classes. # postgresql.conf shared_preload_libraries = 'pg_stat_statements' custom_variable_classes = 'statistics' statistics.max_statements = 1000 statistics.track_statements = on statistics.saved_file = 'global/pg_stat_statements.dump' $ pgbench -i postgres=# SELECT pg_stat_statements_reset(); $ pgbench -c10 -t300 -M prepared postgres=# \x postgres=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3; -[ RECORD 1 ]+------------------------------------------------------------- userid | 10 dbid | 16384 query | UPDATE branches SET bbalance = bbalance + $1 WHERE bid = $2; calls | 3000 total_time | 34683 cpu_time | 1843 gets | 48166 reads | 12 writes | 0 local_reads | 0 local_writes | 0 rows | 3000 -[ RECORD 2 ]+------------------------------------------------------------- userid | 10 dbid | 16384 query | UPDATE tellers SET tbalance = tbalance + $1 WHERE tid = $2; calls | 3000 total_time | 34611 cpu_time | 593 gets | 26447 reads | 7 writes | 0 local_reads | 0 local_writes | 0 rows | 3000 -[ RECORD 3 ]+------------------------------------------------------------- userid | 10 dbid | 16384 query | UPDATE accounts SET abalance = abalance + $1 WHERE aid = $2; calls | 3000 total_time | 546 cpu_time | 359 gets | 18025 reads | 274 writes | 0 local_reads | 0 local_writes | 0 rows | 3000 Takahiro Itagaki itagaki.takahiro@oss.ntt.co.jp