Last active
December 12, 2019 03:41
-
-
Save imbgar/aad8c92cce94bd628c94034c17842c05 to your computer and use it in GitHub Desktop.
postgres
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Identify long running | |
SELECT | |
pid, | |
now() - pg_stat_activity.query_start AS duration, | |
query, | |
state | |
FROM pg_stat_activity | |
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes' order by duration desc; | |
-- Kill it slowly | |
SELECT pg_cancel_backend(PID); | |
-- Kill it quickly | |
SELECT pg_terminate_backend(PID); | |
-- Do I need an index | |
SELECT | |
relname, | |
seq_scan - idx_scan AS too_much_seq, | |
CASE | |
WHEN | |
seq_scan - coalesce(idx_scan, 0) > 0 | |
THEN | |
'Missing Index?' | |
ELSE | |
'OK' | |
END, | |
pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan | |
FROM | |
pg_stat_all_tables | |
WHERE | |
schemaname = 'public' | |
AND pg_relation_size(relname::regclass) > 80000 | |
ORDER BY | |
-- Find unused indexes | |
SELECT | |
indexrelid::regclass as index, | |
relid::regclass as table, | |
'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement | |
FROM | |
pg_stat_user_indexes | |
JOIN | |
pg_index USING (indexrelid) | |
WHERE | |
idx_scan = 0 | |
AND indisunique is false; | |
-- Identify estimate count | |
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment