postgresql pg_stat_statements - statistics on sql statements

  • add the extension to the database:

    echo 'create extension pg_stat_statements;' | psql -U username dbname
  • query statistics:

psql -U username dbname <<EOM
select query, calls, total_time, rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0)
  as hit_percent
  from pg_stat_statements
  order by total_time desc limit 10;
EOM