pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConvenient dump of table and object size in PG 7.4
3+ messages / 3 participants
[nested] [flat]
* Convenient dump of table and object size in PG 7.4
@ 2005-05-27 02:39 Steve Lane <slane@soliantconsulting.com>
2005-05-27 02:48 ` Re: Convenient dump of table and object size in PG 7.4 Alvaro Herrera <alvherre@surnet.cl>
0 siblings, 1 reply; 3+ messages in thread
From: Steve Lane @ 2005-05-27 02:39 UTC (permalink / raw)
To: pgsql-admin
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@soliantconsulting.com
Attachments:
table_size.txt (text/plain, 11.9K) download
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Convenient dump of table and object size in PG 7.4
2005-05-27 02:39 Convenient dump of table and object size in PG 7.4 Steve Lane <slane@soliantconsulting.com>
@ 2005-05-27 02:48 ` Alvaro Herrera <alvherre@surnet.cl>
2005-05-27 03:17 ` Re: Convenient dump of table and object size in PG 7.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Alvaro Herrera @ 2005-05-27 02:48 UTC (permalink / raw)
To: Steve Lane <slane@soliantconsulting.com>; +Cc: pgsql-admin
On Thu, May 26, 2005 at 09:39:26PM -0500, Steve Lane wrote:
> 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.
You could change all those "CASE WHEN foo IS NULL THEN 0 ELSE foo" into
COALESCE(foo, 0).
Additionally, "show block_size" will give you the block (page) size. I
don't see how would you use that in a query though.
--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La tristeza es un muro entre dos jardines" (Khalil Gibran)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Convenient dump of table and object size in PG 7.4
2005-05-27 02:39 Convenient dump of table and object size in PG 7.4 Steve Lane <slane@soliantconsulting.com>
2005-05-27 02:48 ` Re: Convenient dump of table and object size in PG 7.4 Alvaro Herrera <alvherre@surnet.cl>
@ 2005-05-27 03:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-05-27 03:17 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@surnet.cl>; +Cc: Steve Lane <slane@soliantconsulting.com>; pgsql-admin
Alvaro Herrera <alvherre@surnet.cl> writes:
> On Thu, May 26, 2005 at 09:39:26PM -0500, Steve Lane wrote:
>> Object sizes are computed from pg_class.relpages with an assumed page size
>> of 8192.
> Additionally, "show block_size" will give you the block (page) size. I
> don't see how would you use that in a query though.
current_setting('block_size')
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-27 03:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-27 02:39 Convenient dump of table and object size in PG 7.4 Steve Lane <slane@soliantconsulting.com>
2005-05-27 02:48 ` Alvaro Herrera <alvherre@surnet.cl>
2005-05-27 03:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox