Hello all:
Attached is a view I wrote for postgres 7.4 that seems to provide pretty
accurate reporting of table sizes. For each non-system table in a database
it shows base table size, size of any related TOAST data, and aggregate size
of indexes.
The code is somewhat ugly in that it contains a bunch of CASE statements the
sole point of which is to transform the occasional NULL into a 0.
Object sizes are computed from pg_class.relpages with an assumed page size
of 8192.
Comments and improvements most welcome.
-- sgl
--------
Steve Lane
Vice President
Soliant Consulting, Inc.
(312) 850-3830 (V)
(312) 850-3930 (F)
slane@xxxxxxxxxxxxxxxxxxxxx
iep_db=# select * from table_sizes;
Name | Rows | Base Size | Toast Size | Toast Index Size | index_size | Total Size MB
-----------------------------+-------------+-----------+------------+------------------+------------+---------------
iep_log | 1.02768e+07 | 992.95 | 0.00 | 0.01 | 1243.68 | 2236.65
iep_log_old | 8.04244e+06 | 774.64 | 0.00 | 0.01 | 973.30 | 1747.95
student_search4 | 2.09414e+06 | 342.49 | 0.00 | 0.01 | 426.89 | 769.38
iep_form_004 | 78921 | 159.97 | 245.56 | 16.86 | 4.30 | 426.69
iep_form_004_goal | 232900 | 213.50 | 0.70 | 0.03 | 16.81 | 231.04
iep_form_004_goal_progress | 486339 | 131.33 | 0.29 | 0.02 | 43.70 | 175.35
iep_form_002 | 42234 | 50.36 | 67.88 | 0.96 | 1.56 | 120.75
iep_guardian | 160151 | 43.57 | 0.00 | 0.01 | 28.39 | 71.97
iep_form_003 | 74492 | 56.17 | 0.01 | 0.02 | 2.70 | 58.90
iep_form_010 | 165827 | 48.05 | 0.00 | 0.01 | 9.02 | 57.07
iep_student | 104297 | 36.13 | 0.00 | 0.01 | 17.80 | 53.94
deleted_forms | 14769 | 22.84 | 12.50 | 0.18 | 0.00 | 35.52
iep_form_004_supp | 16429 | 13.00 | 19.91 | 0.27 | 0.31 | 33.50
iep_student_team | 138812 | 14.19 | 0.00 | 0.01 | 10.03 | 24.22
iep_form_001 | 15265 | 16.12 | 0.54 | 0.04 | 0.59 | 17.29
iep_form_011 | 20138 | 13.79 | 0.00 | 0.01 | 0.75 | 14.55
iep_session | 5680 | 4.42 | 9.27 | 0.15 | 0.60 | 14.44
iep_form_007 | 12655 | 12.94 | 0.34 | 0.03 | 0.49 | 13.80
iep_form_005 | 13027 | 9.99 | 0.21 | 0.02 | 0.51 | 10.73
iep_form_013 | 2525 | 4.74 | 4.12 | 0.18 | 0.20 | 9.24
ifsp_goals | 7933 | 7.92 | 0.17 | 0.02 | 0.16 | 8.27
iep_form_009 | 10367 | 6.84 | 0.16 | 0.02 | 0.41 | 7.42
iep_message | 16157 | 6.96 | 0.00 | 0.01 | 0.31 | 7.27
iep_form_008 | 6997 | 5.96 | 0.07 | 0.02 | 0.29 | 6.35
iep_personnel | 9480 | 2.80 | 0.00 | 0.01 | 0.82 | 3.63
iep_form_012 | 2268 | 2.65 | 0.52 | 0.03 | 0.11 | 3.32
iep_form_017 | 3242 | 2.40 | 0.66 | 0.03 | 0.16 | 3.26
iep_privileges | 18663 | 2.42 | 0.00 | 0.01 | 0.70 | 3.14
iep_guardian_temp | 17274 | 2.98 | 0.00 | 0.01 | 0.00 | 2.99
iep_transfer_request | 8379 | 1.47 | 0.00 | 0.01 | 1.47 | 2.94
ifsp_team_members | 14672 | 2.42 | 0.00 | 0.01 | 0.29 | 2.71
ifsp_services | 8059 | 2.16 | 0.00 | 0.01 | 0.16 | 2.33
iep_supp | 1086 | 0.76 | 0.93 | 0.03 | 0.08 | 1.81
iep_form_014 | 2053 | 1.49 | 0.00 | 0.01 | 0.11 | 1.61
delete_goal_backup_bug2465 | 1382 | 1.24 | 0.00 | 0.01 | 0.00 | 1.25
iep_goal_objective | 5228 | 0.88 | 0.00 | 0.01 | 0.19 | 1.07
iep_form_015 | 839 | 0.83 | 0.01 | 0.02 | 0.07 | 0.92
ifsp_parents | 4731 | 0.76 | 0.00 | 0.01 | 0.11 | 0.88
ifsp_tran_plan_participants | 3344 | 0.59 | 0.00 | 0.01 | 0.08 | 0.68
ifsp_team_other | 4254 | 0.52 | 0.00 | 0.01 | 0.10 | 0.63
iep_student_teambu | 5747 | 0.60 | 0.00 | 0.01 | 0.00 | 0.61
iep_goal_condition | 2092 | 0.57 | 0.00 | 0.01 | 0.00 | 0.57
iep_student_deleted | 1696 | 0.55 | 0.00 | 0.01 | 0.00 | 0.56
iep_school | 1270 | 0.28 | 0.00 | 0.01 | 0.25 | 0.54
iep_form_016 | 607 | 0.40 | 0.02 | 0.02 | 0.07 | 0.50
iep_rule51 | 1470 | 0.34 | 0.00 | 0.01 | 0.07 | 0.42
iep_school_name | 1263 | 0.19 | 0.00 | 0.01 | 0.13 | 0.33
iep_extracurriculars_saved | 2229 | 0.29 | 0.00 | 0.01 | 0.00 | 0.30
iep_district | 510 | 0.19 | 0.00 | 0.01 | 0.10 | 0.29
iep_form_006 | 280 | 0.20 | 0.01 | 0.02 | 0.03 | 0.26
lps_mdt_paragraphs | 385 | 0.14 | 0.00 | 0.01 | 0.03 | 0.18
iep_survey | 239 | 0.11 | 0.01 | 0.02 | 0.03 | 0.17
iep_school_report_date | 1730 | 0.15 | 0.00 | 0.00 | 0.00 | 0.15
iep_sesis_snapshot2 | 134 | 0.13 | 0.00 | 0.01 | 0.00 | 0.14
iep_sesis_snapshot | 206 | 0.12 | 0.00 | 0.01 | 0.00 | 0.13
iep_school_non_public | 494 | 0.07 | 0.00 | 0.01 | 0.03 | 0.11
iep_messages | 49 | 0.04 | 0.00 | 0.01 | 0.02 | 0.07
iep_county | 95 | 0.02 | 0.00 | 0.01 | 0.03 | 0.06
iep_goal_subtopic | 351 | 0.03 | 0.00 | 0.01 | 0.02 | 0.06
iep_goal_topic | 153 | 0.02 | 0.00 | 0.01 | 0.02 | 0.04
iep_goal_domain | 21 | 0.01 | 0.00 | 0.01 | 0.02 | 0.03
iep_goal_standard | 229 | 0.02 | 0.00 | 0.01 | 0.00 | 0.03
iep_form_newdupes | 4 | 0.01 | 0.01 | 0.02 | 0.00 | 0.03
master_form | 0 | 0.00 | 0.00 | 0.01 | 0.01 | 0.02
iep_accom_checklist | 9 | 0.01 | 0.00 | 0.01 | 0.00 | 0.02
helper_student_team | 3 | 0.01 | 0.00 | 0.01 | 0.00 | 0.02
iep_pageviews | 0 | 0.00 | 0.00 | 0.01 | 0.00 | 0.01
(67 rows)
iep_db=# \d table_sizes
View "public.table_sizes"
Column | Type | Modifiers
------------------+---------+-----------
Name | name |
Rows | real |
Base Size | numeric |
Toast Size | numeric |
Toast Index Size | numeric |
index_size | numeric |
Total Size MB | numeric |
View definition:
SELECT relname AS "Name", reltuples AS "Rows", round(0.008192 * relpages::numeric, 2) AS "Base Size", round(0.008192 *
CASE
WHEN (( SELECT c2.relpages
FROM pg_class c2
WHERE c2.oid = c1.reltoastrelid)) IS NULL THEN 0
ELSE ( SELECT c2.relpages
FROM pg_class c2
WHERE c2.oid = c1.reltoastrelid)
END::numeric, 2) AS "Toast Size", round(0.008192 *
CASE
WHEN (( SELECT c3.relpages
FROM pg_class c2, pg_class c3
WHERE c1.reltoastrelid = c2.oid AND c2.reltoastidxid = c3.oid)) IS NULL THEN 0
ELSE ( SELECT c3.relpages
FROM pg_class c2, pg_class c3
WHERE c1.reltoastrelid = c2.oid AND c2.reltoastidxid = c3.oid)
END::numeric, 2) AS "Toast Index Size", round(
CASE
WHEN (( SELECT sum(c2.relpages::numeric * 0.008192) AS sum
FROM pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid)) IS NULL THEN 0::numeric
ELSE ( SELECT sum(c2.relpages::numeric * 0.008192) AS sum
FROM pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid)
END, 2) AS index_size, round(0.008192 * (relpages +
CASE
WHEN (( SELECT c2.relpages
FROM pg_class c2
WHERE c2.oid = c1.reltoastrelid)) IS NULL THEN 0
ELSE ( SELECT c2.relpages
FROM pg_class c2
WHERE c2.oid = c1.reltoastrelid)
END +
CASE
WHEN (( SELECT c3.relpages
FROM pg_class c2, pg_class c3
WHERE c1.reltoastrelid = c2.oid AND c2.reltoastidxid = c3.oid)) IS NULL THEN 0
ELSE ( SELECT c3.relpages
FROM pg_class c2, pg_class c3
WHERE c1.reltoastrelid = c2.oid AND c2.reltoastidxid = c3.oid)
END)::numeric +
CASE
WHEN (( SELECT sum(c2.relpages::numeric * 0.008192) AS sum
FROM pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid)) IS NULL THEN 0::numeric
ELSE ( SELECT sum(c2.relpages::numeric * 0.008192) AS sum
FROM pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid)
END, 2) AS "Total Size MB"
FROM pg_class c1
LEFT JOIN pg_namespace n ON n.oid = c1.relnamespace
WHERE (c1.relkind = 'r'::"char" OR c1.relkind = ''::"char") AND n.nspname <> 'pg_catalog'::name AND n.nspname <> 'pg_toast'::name AND pg_table_is_visible(c1.oid)
ORDER BY round(0.008192 * (relpages +
CASE
WHEN (( SELECT c2.relpages
FROM pg_class c2
WHERE c2.oid = c1.reltoastrelid)) IS NULL THEN 0
ELSE ( SELECT c2.relpages
FROM pg_class c2
WHERE c2.oid = c1.reltoastrelid)
END +
CASE
WHEN (( SELECT c3.relpages
FROM pg_class c2, pg_class c3
WHERE c1.reltoastrelid = c2.oid AND c2.reltoastidxid = c3.oid)) IS NULL THEN 0
ELSE ( SELECT c3.relpages
FROM pg_class c2, pg_class c3
WHERE c1.reltoastrelid = c2.oid AND c2.reltoastidxid = c3.oid)
END)::numeric +
CASE
WHEN (( SELECT sum(c2.relpages::numeric * 0.008192) AS sum
FROM pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid)) IS NULL THEN 0::numeric
ELSE ( SELECT sum(c2.relpages::numeric * 0.008192) AS sum
FROM pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid)
END, 2) DESC;
