Created
January 3, 2017 23:08
-
-
Save kerbelp/115707c371d733858b1ae0af204b8128 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_encoded_columns_percentage | |
AS | |
SELECT def.tablename, | |
def.mbytes, | |
SUM(CASE WHEN def.attencodingtype = 0 THEN 0 ELSE 1 END)::double precision / COUNT(DISTINCT def.attname)::double precision AS encoded_percentage | |
FROM (SELECT n.nspname AS schemaname, | |
c.oid, | |
c.relname AS tablename, | |
format_encoding(a.attencodingtype) AS "encoding", | |
a.attencodingtype, | |
size.mbytes, | |
a.attname | |
FROM pg_namespace n, | |
pg_class c, | |
pg_attribute a, | |
(SELECT stv_blocklist.tbl, | |
COUNT(*) AS mbytes | |
FROM stv_blocklist | |
GROUP BY stv_blocklist.tbl) size | |
WHERE n.oid = c.relnamespace | |
AND c.oid = a.attrelid | |
AND a.attnum > 0 | |
AND NOT a.attisdropped | |
AND c.oid = size.tbl) def | |
GROUP BY def.tablename, | |
def.mbytes | |
ORDER BY def.mbytes DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment