Created
December 31, 2016 16:23
-
-
Save kerbelp/9bef06b7cbe8efb1d5a3ca35228549e7 to your computer and use it in GitHub Desktop.
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
CREATE OR REPLACE VIEW admin.v_tables_to_vacuum | |
AS | |
SELECT (('VACUUM FULL ' || derived_table1.schemaname) || '.') || derived_table1.tablename AS query | |
FROM (SELECT BTRIM(pgdb.datname) AS dbase_name, | |
BTRIM(pgn.nspname) AS schemaname, | |
BTRIM(a.name) AS tablename, | |
a.id AS tbl_oid, | |
b.mbytes AS megabytes, | |
a. "rows" AS rowcount, | |
a.unsorted_rows AS unsorted_rowcount, | |
CASE | |
WHEN a. "rows" = 0 THEN 0::DOUBLE precision | |
ELSE ROUND(a.unsorted_rows::DOUBLE precision / a. "rows"::DOUBLE precision*100::DOUBLE precision,5::NUMERIC::NUMERIC(18,0)) | |
END AS pct_unsorted, | |
CASE | |
WHEN a. "rows" = 0 THEN 'n/a' | |
WHEN (a.unsorted_rows::DOUBLE precision / a. "rows"::DOUBLE precision*100::DOUBLE precision) >= 20::DOUBLE precision THEN 'VACUUM recommended' | |
ELSE 'n/a' | |
END AS recommendation | |
FROM (SELECT stv_tbl_perm.db_id, | |
stv_tbl_perm.id, | |
stv_tbl_perm.name, | |
SUM(stv_tbl_perm. "rows") AS "rows", | |
SUM(stv_tbl_perm. "rows") - SUM(stv_tbl_perm.sorted_rows) AS unsorted_rows | |
FROM stv_tbl_perm | |
GROUP BY stv_tbl_perm.db_id, | |
stv_tbl_perm.id, | |
stv_tbl_perm.name) a | |
JOIN pg_class pgc ON pgc.oid = a.id::oid | |
JOIN pg_namespace pgn ON pgn.oid = pgc.relnamespace | |
JOIN pg_database pgdb ON pgdb.oid = a.db_id::oid | |
LEFT JOIN (SELECT stv_blocklist.tbl, | |
COUNT(*) AS mbytes | |
FROM stv_blocklist | |
GROUP BY stv_blocklist.tbl) b ON a.id = b.tbl) derived_table1 | |
WHERE derived_table1.megabytes IS NOT NULL | |
AND derived_table1.recommendation = 'VACUUM recommended' | |
ORDER BY derived_table1.megabytes DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment