Created
August 16, 2019 17:33
-
-
Save agarman/aef9389a6f4ff2dd7c6490367b034fc1 to your computer and use it in GitHub Desktop.
Return useful arrangement of stats from PostgreSQL
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
| SELECT | |
| table_name, | |
| pg_size_pretty(table_size) AS table_size, | |
| pg_size_pretty(indexes_size) AS indexes_size, | |
| pg_size_pretty(total_size) AS total_size, | |
| live_tuples, | |
| dead_tuples, | |
| (case | |
| when ((live_tuples + dead_tuples) > 0) | |
| then 100.0 * ((1.0 * dead_tuples) / (live_tuples + dead_tuples)) | |
| else 0.0 | |
| end) as percentage_dead, | |
| seq_scan as scans, | |
| seq_tup_read as selects, | |
| n_tup_ins as inserts, | |
| n_tup_upd as updates, | |
| n_mod_since_analyze as mods, | |
| last_autovacuum, | |
| last_vacuum, | |
| last_analyze | |
| FROM ( | |
| SELECT | |
| pg_table_size(table_name) AS table_size, | |
| pg_indexes_size(table_name) AS indexes_size, | |
| pg_total_relation_size(table_name) AS total_size, | |
| table_stats.* | |
| FROM ( | |
| SELECT | |
| ('"' || schemaname || '"."' || relname || '"') AS table_name | |
| ,n_live_tup AS live_tuples | |
| ,n_dead_tup AS dead_tuples | |
| ,s.* -- select * | |
| FROM pg_catalog.pg_stat_all_tables s | |
| ) AS table_stats | |
| ) AS pretty_sizes | |
| ORDER by live_tuples desc | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment