Changes between Initial Version and Version 1 of Performance


Ignore:
Timestamp:
May 28, 2013, 7:20:50 PM (11 years ago)
Author:
Dimitar Misev
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • Performance

    v1 v1  
     1[[PageOutline]]
     2
     3= Performance =
     4
     5= PostgreSQL tips =
     6
     7== Total size of BLOBs ==
     8BLOBs 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]
     9
     10Query below computes the space that all BLOBs take.
     11{{{
     12SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;
     13}}}
     14
     15== Size of individual BLOBs ==
     16{{{
     17SELECT  loid,
     18        pg_size_pretty(count(*) * 2048)
     19FROM pg_catalog.pg_largeobject
     20GROUP BY loid
     21ORDER BY count(*) DESC;
     22}}}
     23
     24== Size of tables ==
     25{{{
     26SELECT tablename,
     27       pg_size_pretty(size) AS size_pretty,
     28       pg_size_pretty(total_size) AS total_size_pretty
     29FROM (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size,
     30                pg_total_relation_size(schemaname||'.'||tablename) AS total_size
     31      FROM pg_tables) AS TABLES
     32WHERE TABLES.schemaname = 'public'
     33ORDER BY total_size DESC;
     34}}}