Common Postgres SQL Troubleshooting Queries

postgres.sql

-- Heroku has a bunch of awesome queries
-- https://github.com/heroku/heroku-pg-extras/tree/master/commands

-- View slow queries for widgets table
SELECT query, total_time/calls AS avg_time
FROM pg_stat_statements
WHERE query LIKE 'SELECT%'
  AND query LIKE '%widgets%'
ORDER BY avg_time DESC;

-- View size of indexes for widgets table
SELECT c2.relname, c2.relpages * 8 AS bytes
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'widgets' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY bytes DESC;

-- creating indexes concurrently
-- functional indexes (uses lower(email))
CREATE INDEX CONCURRENTLY index_users_on_lower_email ON users (lower(email)) WHERE deleted_at IS NOT NULL;