Changes between Initial Version and Version 1 of Performance

Timestamp:

May 28, 2013, 7:20:50 PM (12 years ago)

Author:

Dimitar Misev

Comment:

—

Performance

@@ +1 @@
+[[PageOutline]]
+
+= Performance =
+
+= PostgreSQL tips =
+
+== Total size of BLOBs ==
+BLOBs are stored in the `pg_largeobject` in postgres. Each BLOB is divided into rows (pages) of 2048 bytes typically. More info [http://www.postgresql.org/docs/8.4/static/catalog-pg-largeobject.html here]
+
+Query below computes the space that all BLOBs take.
+{{{
+SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;
+}}}
+
+== Size of individual BLOBs ==
+{{{
+SELECT  loid,
+        pg_size_pretty(count(*) * 2048)
+FROM pg_catalog.pg_largeobject
+GROUP BY loid
+ORDER BY count(*) DESC;
+}}}
+
+== Size of tables ==
+{{{
+SELECT tablename,
+       pg_size_pretty(size) AS size_pretty,
+       pg_size_pretty(total_size) AS total_size_pretty
+FROM (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size,
+                pg_total_relation_size(schemaname||'.'||tablename) AS total_size
+      FROM pg_tables) AS TABLES
+WHERE TABLES.schemaname = 'public'
+ORDER BY total_size DESC;
+}}}