Quick and dirty postgresql query time analysis rake task

gistfile1.md

Usage

$ rake pg:query_time
total time (min) ▾ | average execution (ms) |            query
--------------------+------------------------+------------------------------
       2.0379       |       1153.5163        | SELECT COUNT(*) FROM "users"  WHERE ("users"."username" ILIKE ?)
       1.3497       |        809.8202        | INSERT INTO users (email, username, auth_token) VALUES (?, ?, ?), ...
       0.0056       |        111.416         | SELECT COUNT(*) FROM "users"  WHERE ("users"."avatar_file_name" ILIKE ?)
(3 rows)

Requirements

• Postgresql
• pg_stat_statments extension

Rake task code

# lib/tasks/rake/pg.rake
namespace :pg do
  task :query_time => :environment do
    sql = %q(
          SELECT
            (total_time / 1000 / 60) as total_minutes,
            (total_time/calls) as average_time,
            query
          FROM pg_stat_statements
          ORDER BY 1 DESC
          LIMIT 20;)

    results = ActiveRecord::Base.connection.execute(sql)

    puts " total time (min) ▾ | average execution (ms) |            query             "
    puts "--------------------+------------------------+------------------------------"

    results.each do |r|
      print r['total_minutes'].to_f.round(4).to_s.center(20)
      print '|'
      print r['average_time'].to_f.round(4).to_s.center(24)
      print '| '
      puts r['query']
    end


    puts "(#{results.count} #{'row'.pluralize(results.count)})"
  end
end